Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using ISNA function, but replace the blank cell with text
I have a project that requires a cell to change with different variables.
The cell can either be blank, use a number from a vlookup that is lower than listed in the vlookup at which time the text message "customer n. than... appears, or the number is in the vlookup range, or the number is greater than the numbers in the vlookup and enters an "NA" result at which I used the ISNA function which then in turn leaves the cell blank. The cell needs to have the text "customer n. invalid" in the cell. The following is the formula I have so far and the only thing it does not do is enter the text "customer n. invalid" when the result is false. =IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater than "&customers!$A$3,IF(VLOOKUP(E8,customers!A3:A11,1) ,"",IF(ISNA(VLOOKUP(E8,customers!A3:A11,1,FALSE)), "Customer number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE))))) I would appreciate any help with this. I have spent many hours trying to make this work. My whole project is full of these type of excel formulas. -- Minnie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using ISNA function, but replace the blank cell with text
Try this:
=IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater than "&customers!$A$3,IF(ISNA(VLOOKUP(E8,cu*stomers ! A3:A11,1,FALSE)),"Customer number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE)))) Hope this helps. Pete On Jun 22, 1:31*am, Minnie wrote: I have a project that requires a cell to change with different variables. * The cell can either be blank, use a number from a vlookup that is lower than listed in the vlookup at which time the text message "customer n. than.... appears, or the number is in the vlookup range, or the number is greater than the numbers in the vlookup and enters an "NA" result at which I used the ISNA function which then in turn leaves the cell blank. *The cell needs to have the text "customer n. invalid" in the cell. *The following is the formula I have so far and the only thing it does not do is enter the text "customer n. invalid" when the result is false. =IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater than "&customers!$A$3,IF(VLOOKUP(E8,customers!A3:A11,1) ,"",IF(ISNA(VLOOKUP(E8,cu*stomers!A3:A11,1,FALSE)) ,"Customer number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE))))) I would appreciate any help with this. *I have spent many hours trying to make this work. *My whole project is full of these type of excel formulas. -- Minnie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using ISNA function, but replace the blank cell with text
That's great Pete! It worked for replacing the "NA" result with "customer n.
invalid" but now when I enter a valid number, the valid number also shows up in the cell that should be blank. -- Minnie "Pete_UK" wrote: Try this: =IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater than "&customers!$A$3,IF(ISNA(VLOOKUP(E8,cuÂ*stomer s! A3:A11,1,FALSE)),"Customer number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE)))) Hope this helps. Pete On Jun 22, 1:31 am, Minnie wrote: I have a project that requires a cell to change with different variables. The cell can either be blank, use a number from a vlookup that is lower than listed in the vlookup at which time the text message "customer n. than.... appears, or the number is in the vlookup range, or the number is greater than the numbers in the vlookup and enters an "NA" result at which I used the ISNA function which then in turn leaves the cell blank. The cell needs to have the text "customer n. invalid" in the cell. The following is the formula I have so far and the only thing it does not do is enter the text "customer n. invalid" when the result is false. =IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater than "&customers!$A$3,IF(VLOOKUP(E8,customers!A3:A11,1) ,"",IF(ISNA(VLOOKUP(E8,cuÂ*stomers!A3:A11,1,FALSE) ),"Customer number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE))))) I would appreciate any help with this. I have spent many hours trying to make this work. My whole project is full of these type of excel formulas. -- Minnie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using ISNA function, but replace the blank cell with text
I think you want to do this, then:
=IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater than "&customers!$A$3,IF(ISNA(VLOOKUP(E8,customers! A3:A11,1,FALSE)),"Customer number is invalid",""))) Hope this helps. Pete On Jun 22, 2:19*am, Minnie wrote: That's great Pete! It worked for replacing the "NA" result with "customer n. invalid" but now when I enter a valid number, the valid number also shows up in the cell that should be blank. -- Minnie "Pete_UK" wrote: Try this: =IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater than "&customers!$A$3,IF(ISNA(VLOOKUP(E8,cu*stomers ! A3:A11,1,FALSE)),"Customer number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE)))) Hope this helps. Pete On Jun 22, 1:31 am, Minnie wrote: I have a project that requires a cell to change with different variables. * The cell can either be blank, use a number from a vlookup that is lower than listed in the vlookup at which time the text message "customer n. than.... appears, or the number is in the vlookup range, or the number is greater than the numbers in the vlookup and enters an "NA" result at which I used the ISNA function which then in turn leaves the cell blank. *The cell needs to have the text "customer n. invalid" in the cell. *The following is the formula I have so far and the only thing it does not do is enter the text "customer n. invalid" when the result is false. =IF(E8="","",IF(E8<customers!$A$3,"Customer number must be greater than "&customers!$A$3,IF(VLOOKUP(E8,customers!A3:A11,1) ,"",IF(ISNA(VLOOKUP(E8,cu**stomers!A3:A11,1,FALSE) ),"Customer number is invalid",VLOOKUP(E8,customers!A1:A3,1,FALSE))))) I would appreciate any help with this. *I have spent many hours trying to make this work. *My whole project is full of these type of excel formulas. -- Minnie- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I replace a ' at the beginning of a text cell using Replace | Excel Discussion (Misc queries) | |||
Replace Function Removes Text Formats In Cell | Excel Discussion (Misc queries) | |||
How Do I find/replace a blank cell with a formula? | Excel Worksheet Functions | |||
Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA | Excel Worksheet Functions | |||
Replace null string with blank cell | Excel Discussion (Misc queries) |