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?