Copy Cell Data into master sheet based on cell value

[Wardylewis]

Good Evening All,

I am creating a project board which has a master dashboard sheet. The idea is that each individual project has its own sheet in the workbook and these are assigned a project number.

Then in a weekly meeting I plan to pull together headline information from the individual project sheets which will be named as just the project number.

In column B this is where I manually enter the project number that we will be reviewing. This relates to the sheet name.

Then from this I would like it to take the information found in cell A5 on that specific sheet and copy it in to my cell on the master sheet.

The current formula is =’928’!A5 which is sheet 928 copy cell A5 data. I really need it to be =’B7’!A5 Cell B7 which is the number 928 find that sheet and copy cell A5.

Is there a correct way of doing this as I cannot seem to get it to work?

Thank you in advance.

 

[offthelip]

The way to do that is to use the "indirect function:

=INDIRECT(B7&"!A5")