Search code examples
google-sheets

Extract Data from Multiple Sheets with Similar Names

I'm trying to put together a sheet that summarizes data from ~70 other sheets, which are all titled by year. What I'm looking to do is find a way where I don't have to hard-code each sheet and can "drag down" the formula and get the other sheets' data.

Here's an example of what I have currently.

Screenshot

For each year, I hard-code the year, e.g. ='2023'!H2, when the year (in the A column) is 2023.

What I'm looking to do is do something like, for instance, ={A2}!H2 to refer to the sheet year - however, this does not work. Any suggestions? Do I need to delve into the world of scripts to get the sheet to do what I want?

Solution

  • You may try this in Cell_B2 & then drag it down:

    =indirect(A2&"!H2")

    Instead of dragging down the formula, you may also opt for this array-style formula (stays only in B2)

    =map(A2:index(A:A,match(,0/(A:A<>""))),lambda(Σ,indirect(Σ&"!H2")))