Linked Cell Question

[cfree36]

I have a data table on Sheet1

On Sheet2 cells are linked to the table on Sheet1 using =Sheet1!A12 etc...

The problem I have is when a row is deleted from Sheet1 the formula on Sheet2 returns a reference. =Sheet1!#REF!

Is there a simple way to link the cell so it will always show what is in cell A12 no matter if rows or columns are deleted?

I know there are methods such as lookups, but I would think there would be another way... where the linked reference would not change?

Any thoughts?

 

[orbea_adam]

Some variation of lookup or index really would be best. Post some sample data so we can help further.

 

[Richard Schollar]

You could use:

=INDIRECT("Sheet1!A12")

Best regards

Richard

 

[Airfix9]

Could use indirect function. If cell B10 on Sheet 2 must always equal Cell B10 in Sheet 1, even if a row on Sheet 1 is deleted, then type "B10" into A10 of Sheet 2. Formula for B10 is '=INDIRECT("Sheet1!"&A2)'

 

[cfree36]

Thanks seems to work great.