Remember Me?

#1
April 8th 08, 09:59 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2008 Posts: 4
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
April 8th 08, 10:17 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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
April 8th 08, 10:46 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 35,218
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post david.karr Excel Discussion (Misc queries) 7 April 7th 07 11:35 PM Pocket Protector as a Fashion Statement Excel Worksheet Functions 1 March 11th 07 08:44 PM Gary Excel Worksheet Functions 16 August 8th 06 08:27 PM jseabold Excel Discussion (Misc queries) 1 January 30th 06 11:01 PM LATATC Excel Worksheet Functions 2 October 20th 05 04:41 PM

All times are GMT +1. The time now is 06:40 AM.