Sheet reference based on another cell's value

Greetings community,

I have a small challenge which I would (ideally) like to solve by the judicious application of a formula.

I have a workbook with many sheets, the data from which is represented on a single overview sheet.

One of the columns on the overview sheet contains the name of the source sheet.

A new requirement has emerged to pull through data from one (fixed) cell on each sheet, and I am hoping to use a formula to achieve that.

Say, for instance, the name of the source sheet (Say "fred") is in cell A2 on the Summary sheet. I want cell J2 on the Summary sheet to display the value from cell E1 on the sheet "fred". (Effectively "=fred!E$1") I would then fill the formula down, so it picks up the sheet name from A3, A4, A5... but the required data is always in cell E1 on the named worksheet.

I have tried various permutations but not hitting the working combination. Any suggestions please?

Re: Sheet reference based on another cell's value

Quote from cytop;771834

It helps to know what you have tried... the issue might be obvious.

Tried the INDIRECT() function?

=INDIRECT(CONCATENATE(B2, "!A1"))

If B2 contains 'Fred' that returns the value in Cell A1 of the sheet named 'Fred'

Display More

Wonderful!!!

I had tried INDIRECT but not in conjunction with Concatenate. I have tweaked a little further since some of my sheet names contain spaces, but this works:

=INDIRECT(CONCATENATE("'",A2,"'!$E$1"))