how can I write a formula that uses a cell's contents when building a range reference to the name of another sheet?

Question

I have a large Google Sheets spreadsheet that has individual sheets for financial statements of activity for multiple years. I want to reference particular columns of those in other sheets, and I've successfully figured out how to do that with an HLOOKUP function. However, because I want to do this for multiple years, I'd like that HLOOKUP function to pick up the name of the sheet to reference from its column header. Right now, I'm hard-coding it like this—you can see the HLOOKUP range refers to cells in the "2021 Overall" sheet. The hard-coded approach works but makes adding a new year tedious. Ideally, the HLOOKUP formula would read the contents of its column header cell to determine which year it is.

As best I can tell, the solution is to use INDIRECT, but I can't figure out any way to build the formulate with INDIRECT and not get an error. For instance, this seemed like it should work. As you can see, I have 2021 in cell D4, and my INDIRECT statement is referencing that and building the rest of the range.

I've also tried using INDIRECT with an explicit CONCATENATE, with no more success.

Any ideas for how to look up that D4 cell and slide it into the HLOOKUP range?

Thank you!

Answer 1

With Nikko's nudges in the right direction, I eventually figured out the right format. This allows the formula to work in multiple sheets and to be filled right (for more years) and down (for more classes).

=IFERROR(HLOOKUP($A$2,INDIRECT("'"D$4&" Overall'!$A$5:$X$150"), Utility!$A3, FALSE))

Note that if you try to replicate this, you may need to type the formula out from scratch—I had a problem where pasting it in didn't work. Once I'd retyped it and Google Sheets acknowledged it, it worked from then on in the spreadsheet, even when pasted from sheet to sheet.

Answer 2

Try to remove the "'"& before D4 and the ' after the Overall.

Your formula should look like this:

=IFERROR(HLOOKUP($A$2,INDIRECT(F4 &" Overall!$A$5:$X$150", Utility!$A10, FALSE)))