Any question answered...

This seems so obvious i'm worried there must be something else wrong, but anyway:

If cell A1 on sheet 2 has anything entered into it, putting the formula =SHEET2!A1 anywhere else in the workbook will make whatever it was appear there too.

That's the problem, it's not working. I thought it was something I was doing wrong! Doesn't seem to matter how the cells are formatted either.

edit: strangely it's working on other parts of the sheet just how I want it to, seems to just be one or two cells.

That's what you get when messing with old workbooks that other people have designed I suppose, we do have somewhat "esoteric" files at work.

Seems sorted now, thanks for your help guys!