Home 
Search 
Today's Posts 
#1




How to get a blank cell instead of a "0"
I used (various and many) spreadsheets at www.contextures.com to create a
2tier dependent droplist 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




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 2tier dependent droplist 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




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 2tier dependent droplist 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 nonblank 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 