Using text from one cell as formula in the next one.

[exarranum]

I have a list of workbook contents and want to set a formula that will automatically use the sheet name (in collumn A) to return a value from said sheet automatically.
For example I want to know the last date that bananas were delivered, with delivery dates found in collumn B sheet "bananas" I can do so manually;
=MAX(Bananas!B:B)

What I want is a way to get the sheet name from adjacent cell, say I have the name of all my products in A and want to know last delivery date in B

=MAX((A1)!B:B)

But this doesnt work, anyway to pull text from collumn A and use it as part of the formula in B?

[benishiryo]

hi exarranum, you need the INDIRECT formula. something like:
=MAX(INDIRECT(A1&"!B:B"))

[exarranum]

Fantastic, exactly what I was looking for. I had never even heard of this formula, thanks very much.