Try it like this...

Assume you want the results to appear starting in cell B1.

Entered in B1 and copied down as needed:

=INDEX(INDIRECT(A$1&"!B5:B100"),ROWS(B$1:B1))

Adjust for the correct end of range in B5:B100

--
Biff
Microsoft Excel MVP


"E" wrote in message
...
That's really helpful, thanks.
Do you know how I can make my 'B5' drag down so that it's B5, B6, B7 etc?
I
fear I need to complicate the function.

"Luke M" wrote:

You need the text part of INDIRECT to be in quotes
=INDIRECT("'"&$A$1&"'!B5")

Note how single quotes surround sheet name, incase you ever have a space
in
one of the names.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"E" wrote:

I want to do something very simple, but I have trouble understanding
the
parts of the INDIRECT function.
I have sheets called Jan, Feb, etc. In a separate sheet I wish to
return the
value of cell B5 for each month in an array. My attempt was
=INDIRECT($A$1&'!B5) where A1 is the month, ie, Jan, Feb. This returns
REF#.
Please could you advise. I assume I am making mistakes with my
ampersands and
inverted commas.

Thanks, E