CODEWITHSUNDEEP
excel
user200814
user200814

Reputation: 275

How do I dynamically choose an excel sheet to reference in a formula?

I have an xlsx with sheets named "A", "B", "C", etc. I need to form a table with the names "A", "B", "C" as the first column, and some data from the same cell in each sheet as the second column. So for example, this might look like:

   Sheet          Data
   A              =A!C2
   B              =B!C2

In my "Data" column, is there some way to reference the names "A" and "B" from the "Sheet" column, so I can just fill the forumla in once and have it update as I add more sheets?

Upvotes: 1

Views: 11051

Answers (1)

Peter L.
Peter L.

Reputation: 7304

Assuming your sheet name A is in A2 - use this in B2: =INDIRECT("'"&A2&"'!C2")

Upvotes: 8

Related Questions