Another Daft Excel Question

[nikaiyo2]

I have an excel workbook that has various worksheets within it.
There is a summary page that copies values from cells within the various sheets.
All simple and works just fine.

However when I insert a column in the individual sheets, it alters the link in the summary sheet. So for instance the summary links to Cell $F$157 when I insert a column this automatically changes to $G$157.

Is there a way to stop this, I want it to continue to point to $F$157, even after having inserted a new column.

[Doofus]

You'll need to use Relative (R1C1) cell references.

[Mr Pointy]

Have a read of this page:
https://support.microsoft.com/en-us/office/switch-...

[i4got]

You may need Indirect() function

eg

To bring in the value in cell H4 from Sheet2 even if you insert columns before column H then it would look like this.

=INDIRECT("Sheet2"&"!"&"H4")

Basically you build up a string referencing the sheet name and the cells.

[Planet Claire]

If it was just a single cell then you could name the cell, for example, Fred, then in your summary sheet just put "=Fred" and it will pull through whatever value cell "Fred" has. It doesn't matter if you insert extra columns or rows, Excel will automatically update the reference cell for Fred.

Edit, didn't read your question properly, in particular the last sentence, so my way wouldn't work for what you want to do, so forget it!

Edited by Planet Claire on Thursday 24th November 07:09

[Jenny Tailor]

=INDIRECT is your friend - as mentioned above.
He nailed it.