referencing another worksheet / passing name from curr. sheet

[Guest]

I need to have some worksheets reference each other. It's probably easiest
to give a simplified example of what I'm trying to do:

WORKSHEET NAME: FRUIT
CELL B8 HAS VALUE "ORANGE"

CURRENT WORKSHEET:
CELL A1 HAS A VALUE "FRUIT" << NAME OF THE OTHER WS


NOW, I WANT CELL A2 IN THIS CURRENT WORKSHEET TO 'GET' THE VALUE "ORANGE"
FROM THE B8 CELL IN THE "FRUIT" WORKSHEET.

I'm trying to do something like:

The formula for cell A2 of the current sheet (if I type it in manually)
turns out to be:

=FRUIT!B8

but I want to be able to pass the worksheet name from cell A1 of the current
sheet, like this:

=A1!B8

but of course that doesn't work. So, how do I automatically pass the value
from A1 of the current worksheet so that it recognizes it as the name of the
other worksheet for this formula?

Thanks,
Drywall Goddess

P.S. Description of actual application: Each worksheet represents a
purchase order for receiving purposes. Sometimes, products from 2 different
vendors are "split" shipped via the same trucker. In this case, some of the
F/A from one vendor is applied to the purchase from the second vendor. Other
information is shared from the second vendor by the first vendor. I name the
worksheets with the purchase order numbers. I have a little switch that
indicates "this" PO is a split shipment. Then I just put in the number of
the 'other' PO and I want the worksheet to go get what it needs from that
other worksheet. Make sense?

 

[Guest]

As a guess you can get away with using the indirect function, something like
this

=indirect(A1) & ":B8"

 

[Ardus Petus]

=INDIRECT(A1&"!B8")

HTH

 

[Guest]

I should give up while I am behind... Ardus is correct in his use of
Indirect...

=Indirect(A1&"!B8")