Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get a blank cell instead of a "0"
I used (various and many) spreadsheets at www.contextures.com to create a
2-tier dependent drop-list for addressing invoices. The third, fourth, and fifth lines use IF functions to call data from a named range on another sheet in the workbook. However, some of the addresses do not have a fifth line. Short of going through all of the entries (over 150), how can I alter the formula (below) to yield a blank cell instead of a zero. =IF(ISERROR(VLOOKUP($D$11,INDIRECT(VLOOKUP($D$10,C HDLookup,2,0)&"Lookup"),4,0)),"",VLOOKUP($D$11,IND IRECT(VLOOKUP($D$10,CHDLookup,2,0)&"Lookup"),4,0))] |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get a blank cell instead of a "0"
You'd have to add another IF(VLOOKUP(.....)=0,"",VLOOKUP(.....)) which will
make the formula pretty long. Or, use a helper cell with just this: =VLOOKUP($D$11,INDIRECT(VLOOKUP($D$10,CHDLookup,2, 0)&"Lookup"),4,0) Then use a formula like this that pulls the result from the helper cell: =IF(ISERROR(helper_cell),"",IF(helper_cell=0,"",he lper_cell)) -- Biff Microsoft Excel MVP "ABlevins" wrote in message ... I used (various and many) spreadsheets at www.contextures.com to create a 2-tier dependent drop-list for addressing invoices. The third, fourth, and fifth lines use IF functions to call data from a named range on another sheet in the workbook. However, some of the addresses do not have a fifth line. Short of going through all of the entries (over 150), how can I alter the formula (below) to yield a blank cell instead of a zero. =IF(ISERROR(VLOOKUP($D$11,INDIRECT(VLOOKUP($D$10,C HDLookup,2,0)&"Lookup"),4,0)),"",VLOOKUP($D$11,IND IRECT(VLOOKUP($D$10,CHDLookup,2,0)&"Lookup"),4,0))] |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get a blank cell instead of a "0"
Just a suggestion...
If a 0 is really being returned, the OP may want to see that. But if the "sending" cell was empty, the OP may want to hide that 0. I'd use something like: =if(iserror(vlookup(...)),"",if(vlookup(...)="","" ,vlookup(...)))) "T. Valko" wrote: You'd have to add another IF(VLOOKUP(.....)=0,"",VLOOKUP(.....)) which will make the formula pretty long. Or, use a helper cell with just this: =VLOOKUP($D$11,INDIRECT(VLOOKUP($D$10,CHDLookup,2, 0)&"Lookup"),4,0) Then use a formula like this that pulls the result from the helper cell: =IF(ISERROR(helper_cell),"",IF(helper_cell=0,"",he lper_cell)) -- Biff Microsoft Excel MVP "ABlevins" wrote in message ... I used (various and many) spreadsheets at www.contextures.com to create a 2-tier dependent drop-list for addressing invoices. The third, fourth, and fifth lines use IF functions to call data from a named range on another sheet in the workbook. However, some of the addresses do not have a fifth line. Short of going through all of the entries (over 150), how can I alter the formula (below) to yield a blank cell instead of a zero. =IF(ISERROR(VLOOKUP($D$11,INDIRECT(VLOOKUP($D$10,C HDLookup,2,0)&"Lookup"),4,0)),"",VLOOKUP($D$11,IND IRECT(VLOOKUP($D$10,CHDLookup,2,0)&"Lookup"),4,0))] -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find non-blank cell values "hidden" under merged regions? | Excel Discussion (Misc queries) | |||
How create blank cell value as the result of Excel "IF" function? | Excel Worksheet Functions | |||
If A3=alpha numeric,"X", if A3=text,"Y", Blank | Excel Worksheet Functions | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |