Supressing Zeros from null lookups

  • Thread starter Thread starter Gaijintendo
  • Start date Start date
G

Gaijintendo

I am filling a 'Top Ten' table with data from another spreadsheet, however
when there is no contents in the linked table, my report shows 0. I would
have expected the default behaviour to leave the cell blank.
Is there anything short of an if statement to fix this? A preference I am
overlooking?
 
That's how Excel treats empty cells - you need to do something like:

=IF(your_formula="","",your_formula)

Hope this helps.

Pete
 
I was really hoping there was another way...
I have made a table of file and sheetnames to allow easy chop and changing
of externally linked files...
so I end up with this behemouth:
=if(indirect("[" & ASSETS!$A$2 & "]" & ASSETS!$A$2 & "!B" &
row(A11))=0,"",indirect("[" & ASSETS!$A$2 & "]" & ASSETS!$A$2 & "!B" &
row(A11)))

:'(
 
Hi

You could just suppress zeros with Tool>options>view>de-select zero values

--
Regards
Roger Govier

Gaijintendo said:
I was really hoping there was another way...
I have made a table of file and sheetnames to allow easy chop and changing
of externally linked files...
so I end up with this behemouth:
=if(indirect("[" & ASSETS!$A$2 & "]" & ASSETS!$A$2 & "!B" &
row(A11))=0,"",indirect("[" & ASSETS!$A$2 & "]" & ASSETS!$A$2 & "!B" &
row(A11)))

:'(

Pete_UK said:
That's how Excel treats empty cells - you need to do something like:

=IF(your_formula="","",your_formula)

Hope this helps.

Pete
Click to expand...
Click to expand...
 
That would then apply to all cells in that workbook.
If you want it to apply only to this cell, Format Custom as

;;

Yes, just two semicolons

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi
|
| You could just suppress zeros with Tool>options>view>de-select zero values
|
| --
| Regards
| Roger Govier
|
| | > I was really hoping there was another way...
| > I have made a table of file and sheetnames to allow easy chop and changing
| > of externally linked files...
| > so I end up with this behemouth:
| > =if(indirect("[" & ASSETS!$A$2 & "]" & ASSETS!$A$2 & "!B" &
| > row(A11))=0,"",indirect("[" & ASSETS!$A$2 & "]" & ASSETS!$A$2 & "!B" &
| > row(A11)))
| >
| > :'(
| >
| > "Pete_UK" wrote:
| >
| >> That's how Excel treats empty cells - you need to do something like:
| >>
| >> =IF(your_formula="","",your_formula)
| >>
| >> Hope this helps.
| >>
| >> Pete
| >>
| >> On Aug 8, 8:21 am, Gaijintendo <[email protected]>
| >> wrote:
| >> > I am filling a 'Top Ten' table with data from another spreadsheet,
| >> > however
| >> > when there is no contents in the linked table, my report shows 0. I
| >> > would
| >> > have expected the default behaviour to leave the cell blank.
| >> > Is there anything short of an if statement to fix this? A preference I
| >> > am
| >> > overlooking?
| >>
| >>
 
This is all really appreciated, however, I tried ;; and it seems to supress
any number, not simply zero. But I never even thought of using formatting to
solve this.

cheers!
 
Back
Top