How to get a blank cell instead of a "0"

A

ABlevins

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,CHDLookup,2,0)&"Lookup"),4,0)),"",VLOOKUP($D$11,INDIRECT(VLOOKUP($D$10,CHDLookup,2,0)&"Lookup"),4,0))]
 
T

T. Valko

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,"",helper_cell))
 
D

Dave Peterson

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 said:
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,"",helper_cell))

--
Biff
Microsoft Excel MVP

ABlevins said:
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,CHDLookup,2,0)&"Lookup"),4,0)),"",VLOOKUP($D$11,INDIRECT(VLOOKUP($D$10,CHDLookup,2,0)&"Lookup"),4,0))]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top