How to reference a cell in a function that's referenced by another cell?

Question

Level 1

4 points

How to reference a cell in a function that's referenced by another cell?

I have a cell, for exemple A1, that references another cell in another sheet. In A2 I have a function that uses the same reference cell from the other sheet. As this other sheet gets changed a lot, I would like to use A1 in the function to indirectly reference the cell from the other sheet, so that when the other sheet gets changed, I would only have to correct one cell (A1). When I try this I get an error (invalid reference), probably because the function receives the text content from A1, and not the reference address. Any way to make this work?

Posted on May 9, 2024 1:04 PM

Reply

Answer 1

Best reply

Badunit

Level 7

26,356 points

May 9, 2024 4:53 PM in response to ffg88

Usually the goal is to design formulas so you don't have to edit any of them when something gets changed. But if there is a cell that moves around and no way to craft your formulas so they point to the correct cell all the time, you can use a different cell as an intermediary so you only have to change the reference in that one cell.

Say the cell that moves around is Table 2::B2

In another cell (one that does not move around), have the formula =Table 2::B2

Wherever you need Table 2::B2 in a formula, refer to this other cell instead.

When you need to change the cell reference, change it in that one cell.

Reply