I have a spread sheet that has a row of calculated dates for a complete year in row 4. cell T4 is the first date in my calendar and is set to be =DATE(Lookup!A5,1,1) where lookup!A5 contains the number "2012". U4 is =t4+.5 and this goe s on to about ABZ4 giving me 2 columns for every day in the year (an AM and PM column). Row 4 is formatted as a date and shows as dd/mm/yyyy.

However when I use formulas that refer to the cells in row 4 (my dates) they all return zero. eg if I set z1 to be =z4 then it displays 0 when formatted as a number or 00/01/1900 when formatted as a date. if z2 is set to ="value is "&z4 I get "value is 0" displayed.

But Copy and paste this range to a new worksheet and it works fine. Ive got a whole load of sheets in this workbook with data validation and conditional formatting. I really don't want to have to start again

Am I missing something really obvious?



There seems to be a lot of this about but not identical to what I'm seeing