can't copy a lookup formula

[Flanagan]

To organise my accounts I have twelve sheets in my calc document - one for each month of the year.

If I enter a formula in the February sheet, e.g. +'January 21.H3 and then copy that formula to the March sheet (after deleting the $ that was automatically generated) it should substitute February for January. Instead it applies a seemingly random reference (at the moment this is October 20.

Any help would be appreciated as it would be nice to select all sheets March - December and hit paste just once.

[mariosv]

The copy process works fine for me, please edit your question, attach a minimal sample file, and details the steps to reproduce the issue.

[Flanagan]

Thanks. I’ve just made the small file and it works OK so I guess there’s something hidden in my main file that’s causing ‘interference’. Hidden sheets in the sequence might have been a problem but I’ve removed those to no avail.

[erAck]

Please state what exactly you are doing. e.g.

• formula in sheet February 21 cell H3 is ='January 21'.H3
• copy that cell to clipboard
• switch to sheet March 21
• go to cell H3 there
• paste
• formula is adapted to ='February 21'.H3

Works perfectly fine.

If it doesn’t for you, then what are you doing different?

Please do not use the Answer field, edit your original question to provide further details. Thanks.

[keme]

A few thougths:

• Are your montly sheets in sequence?
  
  If they are out of order, that explains the failing relative sheet reference.
• The single quotes must enclose exactly the sheet name. Closing quote is missing in your sample formula. Probably a typo, but thought I’d mention it anyway…
• Splitting one dataset to multiple tables is generally a bad idea. Better, use one tab for the entire accounting project, and utilize filtering, conditionals and other data extraction functionality.

[Flanagan]

Thanks for all the input. I had hidden sheets in the sequence. Removing those and starting again worked.