Excel. Replicated cells change format. Why?!

[Gongalong]

Hi folks,

 

I've got a very simple spreadsheet. On the first sheet I've got two columns where staff enter data. The first column is dates (formatted to be in dd-mmm-yyyy format), the second column is text. Those cells are then replicated on other sheets using = etc. For example on the second sheet a cell might say "='All Years'!A6" referring to the first sheet.

 

The problem I've got is that even though there's no data in the first sheet the second sheet shows "00-Jan-1900" for a date field, and "0" for a text field. So it seems to be wanting to interpret the lack of data as a zero. Why?!

 

Anyone know how I can get it to show a blank cell, just like the source data?

 

Thanks

[andy_b]

Complicated.

 

https://support.microsoft.com/en-us/office/display-or-hide-zero-values-3ec7a433-46b8-4516-8085-a00e9e476b03

 

https://excel.tips.net/T002174_Returning_Zero_when_a_Referenced_Cell_is_Blank.html

 

For the most part I use:

 

=IF(ISBLANK(Sheet1!A2),"",Sheet1!A2)

[Gongalong]

Complicated.

Not sure if you're being sarcastic? But thanks for the answer!

[andy_b]

Not sure if you're being sarcastic? But thanks for the answer!

 

Not sarcastic Just something that should be simple - but Excel things

[Gongalong]

Ah good I was certainly confused!