Reference a sheet based on cell value

[liampog]

Hi

I have a Workbook that has a number of tabs along the bottom that are named individual names of employees.

On a front page Worksheet, in Column A, I have the same employee names down the column.

In Column B, I would like to point to B11 on each of the Worksheets that share the same name as the value in the first Column.

I hope this makes sense. I'm sure there's a way to do this but not sure how.

It's essentially a dynamic reference to a cell based upon the value in Column A.

Thanks

 

[6StringJazzer]

Yes with INDIRECT. This will do the trick. But it will not automatically update the reference to B11 if you insert or delete rows or columns.

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

 

[6StringJazzer]

Here is a more robust version that is relocatable and will adjust for inserted rows or columns on the employee sheet.

=INDIRECT("'" & A1 & "'!" & ADDRESS(ROW($B$11),COLUMN($B$11)))

 

[liampog]

6StringJazzer, awesome! Thanks for your help!

And now that you have told me, it's funny because I have used this formula before for the exact same thing, just couldn't remember which formula.

 

[6StringJazzer]

You're welcome! Go ahead and mark my post as the solution to show your thread is solved.