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.


"Techclerk" wrote:

"=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.