Using IMPORTRANGE, INDIRECT, and CONCATENATE together

1
votes

I have a sheet that pulls numbers from several different sheets to amalgamate numbers. Each week, a new sheet is added to the source files, all with the same name. I'd like to update the amalgamated sheet by changing one cell instead of many. When the tab is located in the same Google sheet, this is easily done with INDIRECT.

Right now, the formula in the amalgamated sheet is:

=IMPORTRANGE(M4, "Aug29!$F$2")

That formula is on each line to pull from several different sheets:

=IMPORTRANGE(M5, "Aug29!$F$2")
=IMPORTRANGE(M6, "Aug29!$F$2")

and so on.

Each week, the "Aug29" changes to the new date - "Sep5", "Sep12", etc.

What I'd like to do is use INDIRECT to pull that part of the equation in (from cell Z1) so that I don't have to update each formula.

I was thinking I could use CONCATENATE to create the "Aug29!$F$2" portion of the formula with INDIRECT:

=IMPORTRANGE(M4, CONCATENATE(INDIRECT(Z1), "!$F$2"))

The problem is, the IMPORTRANGE formula requires quotations around the range string, and I can't figure out how to add them.

Weird problem, I'm sure, but wondering if there's anyone with a solution?

2
google-sheetsconcatenationgoogle-sheets-formulaimportrange

2 Answers

0
votes

UNTESTED. Please try something like: 

=IMPORTRANGE(M4,Z$2)

with in Z2 something like: 

=Z1&"!$F$2"

In other words, I think your IMPORTRANGE does not like INDIRECT.

0
votes

Short answer

Use =IMPORTRANGE(M4, CONCATENATE(Z1, "!$F$2")) or =IMPORTRANGE(M4, Z1&"!$F$2")

Explanation

INDIRECT requires a string that represents a cell or range references, like "Z1" but it will increase unnecessarily the complexity of your formula.

If Z1 has the name of the sheet, then just concatenate it by using CONCATENATE, CONCAT or &