Referencing sheets

[Techclerk]

"=Sheet2!B9"

I name my sheets to be the same value as the A collumn. It would help me if
I can replace "Sheet2" portion of the reference with a text from collumn A.

For example: If A1 has text "CARS," then B1 autamatically takes the value
and looks at sheet named "CARS" (=CARS!B9") Then A2 has text "BOATS" and
B2 looks at the sheet named "BOATS" (=BOATS!B9) I want to be a little bit
fancy here. I want the equation in the B collumn to be the same cut and paste
every thime I add "SCOOTERS", "PLANES", etc.

I don't want to manually edit the sheet name every time. Let it get the
sheet name for the A value.

 

[JLatham]

Try using a formula such as:
=INDIRECT(A1 & "!B9")
where A1 contains the name of the sheet as you described. The formula
should fill down the sheet very nicely. Only thing that you'd have to change
would be the specific cell reference to B9 if that needs to change.

 

[JLatham]

Minor, put potentially important change if any of your sheets have spaces in
their names. This will work for such sheets, and those that don't also:
=INDIRECT("'" & A1 & "'" & "!B9")
The odd looking portions are double-quote single-quote double-quote entries
to give sheet names this format 'my named sheet' for a formula that would
like
'my named sheet'!B9
to excel when it evaluates it.

 

[Techclerk]

Thank you. I tried that command but didn't understand it. I appreciate your
help.

 

[JLatham]

Well, you were definitely on the right track and in there digging around to
try to solve your own problem. You get lots of points for that!

Sometimes the nuances and variations of use for many of the worksheet
functions are not intuitively obvious. This variation, as you can see, gives
a good deal of flexibility of usage for you.

Glad I could help and thanks for the feedback.