How to copy data from one worksheet to another

Question

Hello.

I want to copy some data from one cell in worksheet 1 to another cell in worksheet 2 if it meets some condition. So I am using:

=IF(Sheet3!C49=0,"",Sheet3!C49)

where If cell C49 in sheet 3 has something in it then copy whatever is in cell C49 in sheet 3 into the cell that contains this formula.

This works perfectly for text but when I enter a date of 31/07/2009 in cell C49 then the cell with the above formula says 40025 (all dates give odd numbers). The whole of column C has dates in it. When I enter an integer in cell C49 in sheet 3 (eg. 12), then cell C49 in sheet 3 says "12/01/1900" but the cell in the other worksheet which contains the above formula says 12.

What I want to do is copy the date from cell C49, sheet 3, into the cell with the above formula.

Can anyone help?

Answer 1

Have you tried formatting the column containing the funny number as Date? The funny number is Excel's serial representation of the date.

Alternatively if you just want the date as text you could use the function =TEXT(A1,"yyyyMMdd") etc...

If you wish to copy both the values and the formatting then you'll probably want to knock up a VBA macro.