Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find non-blank 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


All times are GMT +1. The time now is 08:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"