ms excel use value in cell as tab name in formula 2

[straybullet]

I currently have each sales rep's information in separate tabs, each of which is named a rep's last name. Now, I'd like to set up a single summary sheet. Is there a way to use the cell containing the rep's name in the formulas to substitute for the tab name?

ex: =A1!B6

where
A1 is the cell in the summary sheet containing "Smith"
and
B6 is the cell on the Smith tab

=A1!B6 doesn't work, it opens an 'Update values' dialog...

Let them hate - so long as they fear... Lucius Accius

 

[petrosky]

Hi straybullet,

Check the answer here...

Create a Reference To a Different Sheet

http://www.contextures.com/xlFunctions05.html

HTH.

Peter.

Remember- It's nice to be important,
but it's important to be nice

 

[Salut39]

Hi! you can use:

=INDIRECT($A$1&"!$B$6")

Yuri