Reference Google sheet via INDIRECT or Variable

Question

I am trying to have a sheet like this:

April | A2 of Sheet named April

March | A2 of Sheet named March

etc.

I want to be able to just paste data in these monthly sheets and simply drag the whole row in the main sheet down and it should populate the data fields from the correct sheet.

Is this possible in Google Spreadsheets? Is INDIRECT the correct function for this?

What I tried:

=INDIRECT('A2'!B2)

This should give me B2 cell content of the sheet named as whatever is displayed in the current sheet's A2 cell, but it doesn't, I get an error instead.

Relevant Links, which failed to help me:

• https://support.google.com/docs/answer/3093377
• https://support.google.com/docs/answer/75943

Answer 1

I found the answer:

=INDIRECT(A9&"!B2")

A9 = Cell, which contains the name of the sheet you need to referrence B2 = Cell with data in sheet you need to referrence