Cell reference

E

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

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
 
S

swatsp0p

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

Cheers!

Bruc
 
G

Gary L Brown

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