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

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
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

You can only change Border font or pattern with CF.
--
HTH...

Jim Thomlinson


"Daniel.C" wrote:

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
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

Well, now, you can... with XL2007.
HTH
Daniel

You can only change Border font or pattern with CF.
--
HTH...

Jim Thomlinson


"Daniel.C" wrote:

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
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

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;

--
HTH...

Jim Thomlinson


"SeanF74" wrote:

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