I want the formulas in Sheet02 to remain intact even after moving or deleting the cell data in Sheet01. The formulas in Sheet02 should always show what is in Sheet01 A1, A1, B1, B2, etc. When I move the cell A1 to C1, the formula in Sheet02 automatically turns to =Sheet01!C1 and I want it to remain at =Sheet01!A1.
Sheet01
| A | B |
|---|---|
| A1 | B1 |
| A2 | B2 |
Sheet02
| A | B |
|---|---|
| =Sheet01!A1 | =Sheet01!B1 |
| =Sheet01!A2 | =Sheet01!B2 |
I have tried with VLOOKUP, but same result. Help apreciated.
CodePudding user response：
I dont know if this is best solution but it works:
Write your formula as =INDIRECT("Sheet01!A1") it will not change whatever happens in Sheet01 but problem is you cant Copy/Fill this formula and each formula has to be writen separatly,
But if you want to return cells in same position but from Sheet01 you can adjust it like this: =INDIRECT("Sheet01!"&ADDRESS(ROW(),COLUMN(),4,1))
Hope it will help.