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 have the following formula: IF(SELL_INVESTMENT = "YES" , 1,2) It returns not 1 or 2 but 0. The worksheet contains over 1000 cells. Trace Precedents verifies that the formula evaluates the correct cell and that it contains "yes". I created a very simple worksheet to test the formula and here it correctly returns 1.
I have 4 conditions in one formula, and only the first condition (when met) evaluates correctly. When the other conditions are met, I get a VALUE# error.
I want to concatenate 2 cells, based on whether there is an "a", "b", "c", or "d" in a particular cell. see example. Below is my formula:
I am currently creating a form to hold 2 drop down lists that change depending on the selection in the first one. I had never done this before and taught myself and got it to work minus one problem. As soon as I input the formula under the Data Validation the error: "The Source currently evaluates to an error. Do you want to continue?" pops up. Right now the formula works except for the last 6 options in my first drop down. When I select them nothing pops up in the 2nd drop down menu. I have double checked and triple checked that I have no spaces or anything in my LIST on worksheet 2 or any of the names are input correctly.
A7 is the first drop down - PROPOSED
B7 the second. - ITEM #
Lists and data to populate the lists are on worksheet 2 with the "List" at the bottom of worksheet 2...
I need conditional formatting formula that evaluates the values in a row of cells (7 total columns) and counts the number of entries that are 30 or greater. If there are at least three, then I need to apply formatting.
I am trying to return a TRUE or FALSE based on a date in a cell.
for example:
if cell A1 = 07/11/2009
I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.
I have tried stuff like:
=IF(A1=TODAY()-14,"True","False")
but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
I received the following data validation error: Source evaluates to an error. Do you wish to continue? I received this error after attempting to reference a named range on another workbook.
Steps I took:
1) Named the range E5:E123 on the set-up workbook Students8
2) In the Reading8 workbook, I created a name. I called it names and I had it reference =set-up.xls!Students8
3) In the cell, I selected Data-Validation. Allow--List Source =Names
I'm trying to get a "simple" function to return a number and not the #NAME?error!
I'm writing the following vba code. It's located in the sheet1 vba code area.
On the spreadsheet itself, on sheet1, column C, there is data from row 1 down to row 55. I have tried formatting the Column as General, text,etc.
Function Asset() As String Asset = Range("C1").End(xlDown).Row End Function
When I type into the spreadsheet =Asset() , I get the #NAME? instead of the number 55. When I step through in debug mode, Asset shows Asset="55" , but my function doesn't return it!
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.
I am trying to use the IF function to return a false value if the combined sum of a row is not the same as either of the 1st 2 cells. If that makes any sense!
This is the formula I would like to use. =IF(SUM(g3:g9)=g1 or g2 then y if true and x if false. I thought it would be easy but I can't figure out how to do it
I have following snippet of code. it is very simple.
Code: Sub GetDatafromXmlToTemp() Dim str As String Sheets("sheet1").Select desc = WorksheetFunction.Match("FAMILY_ID", Rows("1:1"), 0) ' gives column number for ex 4 for "D"
[Code] .....
I am getting error "Argument not optional" in line.
The function "VAL" will return the value of a numerical string as a double. Is it possible to get a similar function that will return the value as a long.
New to creating macro's but looking for a macro or a function to get the below results. The first 2 columns are givens and looking to return the 3rd column. Is there any code that can do this?
I have a cell with a text string >3, and would like it to return the number (not text) 3. I could use Right but since the number will not only be single digit I don't think this will work.
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
I am looking for a formula that will yield the following: IFAH9>AI9 return the word Green, IFAH9=AI9 return the word Blue, IFAH9<AI9 return the word Yellow. If AI9 is blank stay blank.
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.
I try to concatenate some 5 text strings and I would like to have a carriage return after each text string (ALT ENTER). How can I achieve this using =CONCATENATE(......;......;......;......;......)?
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.
