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.

 How to get a blank cell instead of a "0"
 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"

#1
April 8th 08, 09:59 PM posted to microsoft.public.excel.worksheet.functions
 ABlevins external usenet poster 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
 T. Valko external usenet poster 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
 Dave Peterson external usenet poster Posts: 35,220
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 Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 01:20 AM.