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))] |
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))] |
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 |
All times are GMT +1. The time now is 11:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com