A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Display Modes
  #1  
Old April 8th 08, 09:59 PM posted to microsoft.public.excel.worksheet.functions
ABlevins
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))]
Ads
  #2  
Old April 8th 08, 10:17 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
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  
Old April 8th 08, 10:46 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 35,220
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 07: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 10: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 05:02 PM.


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