Cell reference

[exploringexcel]

I am working with an excel file that has multiple sheets-different views
for different people. However, there is one main sheet that pulls in
data from the other sheets for a consolidated view

My problem is when I create a cell reference, the format is displayed
even when the cell referred to is blank. So for example, if I have a
cell that is formatted to be a date, but does not have data in it yet,
the corresponding cell on the main sheet shows 1/1/00. I want this cell
to be just blank till the time I have real data entered--anyway of doing
this??
Please help!

 

[swatsp0p]

A couple of things you could do:

A) Tools>Options|View tab uncheck "Zero values"

B)Use an IF statement to show value only when reference cell is not
zero, e.g.:

=IF(SHEET1!A1=0,"",SHEET1!A1)

HTH

 

[exploringexcel]

Bruce,

Thanks for your helped--that worked! :

 

[swatsp0p]

I'm glad it worked for you. Thanks for the feedback, it is alway
appreciated.

Cheers!

Bruc

 

[Guest]

Example..
=IF(LEN(Sheet2!B2)=0,"",Sheet2!B2)
vs.
=Sheet2!B2
will only show a value if there is something in Sheet2, Cell B2.

HTH,

 

[John Michl]

Instead of =A1 try

=if(isblank(A1),"",A1)

- John Michl