Blank Cell Reference, without if statement


S

SeanF74

I have a worksheet which has some blank cells on it, and other cells with
zeros. I am trying to reference it in another worksheet, formatted as a
number with one decimal place. When I reference the blank cell, it comes
across as zero. Here is a case example:

Sheet 1, Column A, rows 1-5
2.3
<--Blank cell
1.5
0
3

When referenced on sheet 2:
=Sheet1!A1
=Sheet1!A2
=Sheet1!A3
=Sheet1!A4
=Sheet1!A5

Comes across as:
2.3
0.0 <--Blank on Sheet 1
1.5
0.0
3.0

Is there any sort of formatting that I can do to the cell range on sheet 2
that will reflect the blank cell WITHOUT using an If statement.

thanks,

sean
 
Ad

Advertisements

D

Daniel.C

You can use conditional formating. Select cells Sheet2!A1:A5 and enter
conditional formating using the following formula :
=Sheet1!$A1=""
and entering the number format :
;;;
HTH
Daniel
 
J

Jim Thomlinson

You can not remove the zero but you can change the formatting so that it does
not show... 2 ways...

1 - hides all zeros
Tools -> Options | View | Uncheck Zero Value

2 - hides select zeros
Add a custom format to the select cells something similar to this
Format -> Cells | Number | Custom -> #,##0; -#,##0;
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top