I have a list of items in Sheet1 column A (starting from row 1).
I would like the corresponding column B to have certain values depending on the value in column A (same row), as follows:
If the value in column A has "SYS????CZ", column B should be "HPC" If the value in column A has "SYSNIS", column B should be "NIS" If the value in column A has "SYSJBE", column B should be "JBE" If the value in column A has "ICG????", column B should be "HPC" If the value in column A has "IL????", column B should be "RUP" If the value in column A has "SYSHPC08", column B should be "HPC"
What I tried was to record a macro while typing the formula in B1: ...
I'm receiving this message when I attempt to use the Find, Replace function. Formula is too long". I have a column of cells containing text only. ( about 2-3 paragraphs worth) I'm trying to replace a name with another name, which works fine where the cell contains a single or few sentences, but fails to replace when the cell contains too much information.
I am trying to use a function to look at the value in one cell and return a value to the cell with the functional equation. I think I use the "IF" Function. But it is a little more complicated than I can do. In my sheet, there is a number in D3. I want to create an equation in D4 that evaluates the value in D3. If D3 is between 0 and 100, I want the equation in D4 to return the value 1; If D3 is greater than 100 but less than or equal to 200, I want D4 value to be 2; if D3 greater than 200 but less than or equal to 300, D4 return 4. I would like this to continue in increments of 100 until D4 equals 10.
When I try a MATCH function, I substituted the range through the ADDRESS function. But, it returns #VALUE error. When I manually typed the same range address, it produces the results. I've been behind this more than 8 hours still I can resist the heat on my cool head! But, I thought you coolest head guys need some heat to warm up for to-day's challenge.
The function must be present in all 99 rows to accommodate potential data, but there is rarely enough data to populate the entire sheet, leaving #REF! errors in some cells. Putting aside that using this many array formulae is sloppy programming, how do I craft the function above to return no value if the function evaluates to an #REF! error?
I've searched for an example of function that returns a variable, however, I'm unable to find one other than the one just has return variable on the end. I was wondering what I have wrong in the following code. This is placed in a module to make it available to all my worksheets.
Public Function findLR(Worksheet, Column) As Integer LR = Worksheet.Cells(Rows.count, Column).End(xlUp).Row return LR End Function
The function Hlookup returns #N/A(?) if the value I'm looking for is not found. Is it possible to change this return-value. For exampel to #No or #No value ? I suppose I then need the code of this function? And I cannot get it?
I'm working on a spreadsheet that I need to return a value to "Unit Price" field in worksheet "Master Inventory" based on matching the "Product" field and the "Construction" field from the "Unit Pricing" worksheet.
In essence, I would like the "Unit Price" field to match the "Product" field from the "Master Inventory" sheet to the "New Product Description" field on the "Unit Pricing" sheet, then match the "Construction" field on the "Master Inventory" sheet to the column headers on the "Unit Pricing" sheet and return the value that corresponds to both criteria.
Ex: On the "Master Inventory" sheet, I would like the "Unit Price" field to match the "Product" (Book Browser) to the "New Product Description" (Book Browser) on the "Unit Pricing" sheet and then return the value where the "Construction" (Laminate) matches the column header (Laminate) on the "Unit Pricing" sheet which would return the value of "$240.00".
I've tried using a vlookup function, vlookup/match function, index/match function and an index/match/match function. I've attached a sample workbook.
a column contains dates in date format, contains a date before the column b date b column contains today() I want c column to get some output, if b-a is more than 5 years (exact days), say 20 do , if b-a is more than 2 years and less than 5 years, say 40 do , if b-a is less than 2 years , say 60 otherwise blank use if condition to get the above results. I have formatted a, b with date formats
I have attached a sheet with what I am trying to accomplish...
I have several records that I am doing a lookup through. Most records have the same "work order number". As you can see on the attached sheet, my lookup function returns all the work order numbers that match my criteria.
What I need is to only return the first instance of the work order number then return the next unique work order number that meets my criteria.
What I'm trying to do is get the MAX function to return a value, but that value needs to come before the values reach zero. It's a cubic equation that basically starts, at the beginning of the data range, under 10 then hits zero and ends up near 30 at the end of the data range. The end data range max doesn't matter to me if the data range hits zero along the way. Is there any way to make the MAX function stop at a certain value, or is there another function that could accomplish what I'm trying to do?
I don't really know VBA more than just piecing together other people's work (so please dumb down your responses as much as possible).
So I would like to have a function that displays a filename within a cell. The filename I need to display will start with "cost" and have a date at the end (not always the same number of characters). I need to search a folder for all files that contain that text and return the last modified one.
I found this VBA code that returns the last modified file, but only filters based on the extension (which I would like to keep in the code as well).
I am wondering about the best syntax for using a VLOOKUP return as the row number in a CORREL function. I want to create rolling correlations from today's date. I have a VLOOKUP function that will return the row number corresponding to the chosen day's date. I now need to use that returned value in the CORREL function. That is, I would like it to look something like:
I am using Excel 2002 and trying to do something which should be simple with MATCH.
I have an array of numbers ordered, starting at 0 and incrementing by 0.05 up to 20. I am then using the MATCH function to find the relative position of numbers 0,1,2,3,4 and 5. However, for number 0,1 and 2 the return value is one less than what it should be, for the numbers 3, 4 and 5 it is working as expected. By the way, the match_type I am using is the default 1.
Also I have formatted everything to be numbers and tried various things already, so am beginning to wonder is this is a known issue.
I need help on using the If function. Basically I am trying to calculate the cost of shipments. For example, cost of sending 1 box is 150 (eg cell A3), 2 boxes is 280 (cell A4), 3 boxes is 400 (A5), 4 boxes is 500, etc upto to 10 boxes (cell A12).
What I am looking for is cell A1 to provide me the cost based on the manually entered value of cell A2, i.e. if I enter a number between 1 to 10 in cell A2, then A1 should return a number from A3 to A12, e.g. enter 3 in A2 then A1 should return 400.
Is there such a formula or do I have to enter manually the amounts into an If function?