Update the reference in Indirect() when new columns are added

Question

I have a a very large spreadsheet that pulls data from another logsheet through an Indirect depending on user input on cell A1.

=INDIRECT($A$1&" Data!$AG$3")

I'm trying to add some new columns now to the logsheets, when I manually type in the sheet names

='June Data'!$AG$3

It automatically updates the $AG$3 part when I add new columns, but with indirect() it does not. Is there any way using indirect, or some other formula, where I can get Google Sheets to automatically update my referenced cells?

Answer 1

You should mark column AG in some way so it can be identified as the column that indirect needs. For example, by placing "Data" in the first row of that column.

Then you can use match to find the number of that column, which becomes a part of cell reference in R1C1 notation.

=indirect($A$1 & " Data!R3C" & match("Data", 1:1, 0), false)

This formula refers the cell in 3rd row of the column with header "Data", on the sheet specified in cell A1.

Answer 2

I was able to solve the issue by having a cell $B$1 keep track of how many columns each set of data has, then multiplying the current row number by the number of columns in the set.

So since there were 10 rows per set, $B$1 = 10. Every row this is pasted down gives row 3, column 10,20,30,40,etc.

INDIRECT($A$1&" Data!R3C"& ((ROW())*$B$1) &"",FALSE)

The full code to pull all of the 3rd row data from each set, ignoring blanks ended up being this.

=IF(INDIRECT($A$1&" Data!R3C"& (ROW())*$B$1 &"",FALSE)="","",Filter(INDIRECT($A$1&" Data!R3C"& (ROW())*$B$1 &":R3C"& ((ROW())*$B$1)+$B$1 &"",FALSE),NOT(ISBLANK(INDIRECT($A$1&" Data!R3C"& (ROW())*$B$1 &":R3C"& ((ROW())*10)+$B$1 &"",FALSE)))))

Answer 3

After a lot of unsuccessful attempts, I was finally able to solve the issue with a simple change in the formula : add the function =Now() in such a way that it does not affect the output but does do the job of registering any changes, including the changes in the tab/sheet name, and therefore, forcing the formula to refresh / recalculate.

So, for =INDIRECT($A$1&" Data!$AG$3"), one simply needs to change it to something like : =If(ISNUMBER(NOW),INDIRECT($A$1 & " Data!$AG$3")).

Answer 4

Use the cell() function to retrieve the address of the cell as text, like this cell("address",Data!$AG$3)

So in your example,

=INDIRECT($A$1&" Data!$AG$3") becomes

=INDIRECT($A$1& " & cell("address",Data!$AG$3))