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!


--
exploringexcel
------------------------------------------------------------------------
exploringexcel's Profile: http://www.excelforum.com/member.php...o&userid=26356
View this thread: http://www.excelforum.com/showthread...hreadid=398185

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,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"exploringexcel" wrote:


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!


--
exploringexcel
------------------------------------------------------------------------
exploringexcel's Profile: http://www.excelforum.com/member.php...o&userid=26356
View this thread: http://www.excelforum.com/showthread...hreadid=398185

Instead of =A1 try

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

- John Michl

A couple of things you could do:

A) ToolsOptions|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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=398185

Bruce,

Thanks for your helped--that worked! :)


--
exploringexcel
------------------------------------------------------------------------
exploringexcel's Profile: http://www.excelforum.com/member.php...o&userid=26356
View this thread: http://www.excelforum.com/showthread...hreadid=398185

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

Cheers!

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=398185