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


    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'

  • Re: Sheet reference based on another cell's value



    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"))