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?


    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


    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:


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!