Formula to get data from a tab that is identified in another cell

[Sweet1966]

I'm having a difficult time writing a formula to copy the data into one worksheet from another worksheet.

My file has 3 worksheets: (1) Data, (2) Communications (3) Supply Chain
Data Worksheet: Cell B1: Communications
Data Worksheet: Cell B4: This should populate with line 2, cell A2 from the Communications worksheet.

If I change Cell B1 to Supply Chain, then I want Cell B4 to populate with line 2, cell A2 from the Supply Chain worksheet.

Sounds simple enough but I can't figure it. Appreciate any help.

 

[DanteAmor]

Try this

Formula in cell B4

=INDIRECT("'"&B2&"'!A2")

 

[Sweet1966]

Thanks but it didn't work.

 

[JoeMo]

In cell B4 of Data worksheet:

=INDIRECT("'"&B1&"'!A1")

 

[DanteAmor]

Thanks but it didn't work.

Try this please:

On the sheet where you have cell B4 that you want to fill

Formula in cell B4

=INDIRECT("'"&B1&"'!A2")