CODEWITHSUNDEEP
google-sheets
Quentin
Quentin

Reputation: 1781

How to dynamically set tab name in a formula?

I have a summary tab and multiple other tabs which are constructed the same way. I would like in my summary tab to gather information from all other tabs

Today, in the summary tab I have for each line:

I would like to use the tab name defined in the first cell inside the formula of the second cell. Is it possible and, if yes, how?

Upvotes: 2

Views: 7393

Answers (2)

player0
player0

Reputation: 1

shorter solution:

=COUNTIF(INDIRECT($A$1&"!B7:B"), "OK")

and for dragging it would be:

=COUNTIF(INDIRECT($A$1&"!B"&ROW(B7)&":B"), "OK")

Upvotes: 1

Daniel Ashton
Daniel Ashton

Reputation: 1438

Try the INDIRECT function, described here: https://support.google.com/docs/answer/3093377?hl=en

=INDIRECT($A$1)

In order to be seen as a reference for the range, INDIRECT needs the entire range reference as a single string, it seems. This gives you something like the answer to your question:

=COUNTIF(INDIRECT(CONCATENATE($A$1, "!$B7:$B")), "OK")

What you give up there is that the $B7:$B portion of your address is now a string, so if you copy this cell down the page, the 7 won't get updated to 8, 9, and so on.

There may be other combinations of functions that will get you closer: this is the one I arrived at with a few minutes of searching.

Upvotes: 5

Related Questions