[Dropped] Calc absolute cell references were changed

Discuss the spreadsheet application
Locked
DAVEMAN999
Posts: 1
Joined: Wed Jun 16, 2021 7:29 pm

[Dropped] Calc absolute cell references were changed

Post by DAVEMAN999 »

I compiled a spreadsheet where data is added to a new sheet which is processed via a lookup on a master sheet.
The relevant function is typically {=VLOOKUP($D197;$Sheet16.$C$1:$D$140;2;0)}. However, when I add data to a new sheet the formula changes of its own accord to =VLOOKUP($D197;$Sheet16.$D$1:$E$140;2;0) i.e. shifting the columns one cell to the right. Therefore each time I add another sheet I have been having to manually change the cell references back for it to work. Any help would be appreciated.
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Calc absolute cell references

Post by MrProgrammer »

Hi, and welcome to the forum.
DAVEMAN999 wrote:However, when I add data to a new sheet the formula changes of its own accord to =VLOOKUP($D197;$Sheet16.$D$1:$E$140;2;0) i.e. shifting the columns one cell to the right.
Attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Tell us exactly what steps we can take with that attachment to reproduce the problem which you are experiencing. Indicate which cell on which sheet contains an unexpected formula as a result of performing those steps. Tell us what formula you would expect instead.
DAVEMAN999 wrote:Any help would be appreciated.
Until you show with an attachment what you've done when you "add data to a new sheet", I can't tell you why the formula was changed, though I can refer you to this tutorial.
[Tutorial] Formula Adjustments during Copy and Move

Two other tutorials may not necessarily provide a solution to this specific problem, but they may be helpful for you.
[Tutorial] Ten concepts that every Calc user should know
[Tutorial] VLOOKUP questions and answers
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Locked