0

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?

Improve this question
0

1 Answer 1

Reset to default
1

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")))
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Ah, =indirect() seems to be exactly what I was looking for. Thanks!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.