If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#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))] 
Ads 
#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 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
How to find nonblank cell values "hidden" under merged regions?  david.karr  Excel Discussion (Misc queries)  7  April 7th 07 11:35 PM 
How create blank cell value as the result of Excel "IF" function?  Pocket Protector as a Fashion Statement  Excel Worksheet Functions  1  March 11th 07 08:44 PM 
If A3=alpha numeric,"X", if A3=text,"Y", Blank  Gary  Excel Worksheet Functions  16  August 8th 06 08:27 PM 
Complex if test program possible? If "value" "value", paste "value" in another cell?  jseabold  Excel Discussion (Misc queries)  1  January 30th 06 11:01 PM 
Changing "returned" values from "0" to "blank"  LATATC  Excel Worksheet Functions  2  October 20th 05 04:41 PM 