look up value in another sheet by referencing a cell containing a sheet name

    I'm guessing I need to use the indirect formula somehow, but not quite sure how....


    Sheet1 range D3:D34 for example contains the names of Sheets 3 through 34. So Sheet1!D3 might read Sheet10, Sheet1!D4 might read Sheet17, etc.


    In Sheet1!G3, I want to look up the value from Sheet1!D3 and return the value in cell J40 of the corresponding sheet name.

    Re: look up value in another sheet by referencing a cell containing a sheet name


    Hi and welcome to the forum.


    If Sheet1 Cells D3 contains the text Sheet10, then to return the value from Cell A1 on Sheet10 you would enter:


    =INDIRECT("'" & D3 & "'!" &"A1")


    Note that the D3 cell reference is surrounded by a single quote (within double quotes) on the left and a single quote and exclamation mark (again within double quotes) on the right.

Participate now!

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

Register Yourself Login