[Solved] Get Around Updating of Absolute References

[Mung]

Hello,
When a row or column is inserted absolute references are updated. How can I avoid or get around this?
To demonstrate if A1 contains =$D$1. If a column is inserted b/n columns A to D then A1 becomes =$E$1 returning the same value for A1 as column D has been displaced to the column E position. I want A1 to = the new value in D1, how do I get this? ThanKs for your time.

[Mung]

Sorry, Should have said colomn inserted. Mung

[Richarda44]

Menu > Tools > Options > calc > general > tick the expand ref box

[MrProgrammer]

Hi, and welcome to the forum.

See first response in Calc references across sheets while inserting rows

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Villeroy]

Option "Expand references" has nothing to do withit.

To demonstrate if A1 contains =$D$1. If a column is inserted b/n columns A to D then A1 becomes =$E$1 returning the same value for A1 as column D has been displaced to the column E position.

A1 =OFFSET($A$1;0;3) [no, this won't return a circular reference error]

[Mung]

Hello,
Thank you MrProgrammer for your warm welome to the forum. Thanks also to Richarda44 and Villeroy for their suggestions. I was unsuccessful with "expanding references" but the OFFSET function will do the trick. Thanks all. Mung