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

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

E wrote...
....
=INDIRECT($A$1&'!B5) where A1 is the month, ie, Jan, Feb. This returns REF#.
....

Your formula above is syntactically invalid, so it seems you actually
have something different in Excel than what you posted to the
newsgroup.

Try

=INDIRECT("'"&$A$1&"'!B5")

Try it like this:

A1 = Jan (as a TEXT entry)

=INDIRECT(A1&"!B5")

Which evaluates to: =Jan!B5


--
Biff
Microsoft Excel MVP


"E" wrote in message
...
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

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

Just worked it out, using ROW. Thanks.

"E" wrote:

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

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

"T. Valko" wrote...
....
Entered in B1 and copied down as needed:

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

Ugh! That's a technical assessment.

If the OP wants the value from cell B1 in the worksheet named in cell
A1 in the current worksheet in cell B1 in the current worksheet, more
general to use

=INDIRECT("'"&$A$1&"'!RC",0)

Rule-of-thumb: if you need to use INDIRECT for relative references,
there's NEVER a good reason to use A1-style referencing in INDIRECT's
first argument.

That's a generic formula that can be entered anywhere.

The OP didn't say where the formula would be entered. I don't like using
ROW(), COLUMN() or the equivalent "RC". Depending on where the formula is
entered then you might have to calculate an offset.

--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
Entered in B1 and copied down as needed:

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

Ugh! That's a technical assessment.

If the OP wants the value from cell B1 in the worksheet named in cell
A1 in the current worksheet in cell B1 in the current worksheet, more
general to use

=INDIRECT("'"&$A$1&"'!RC",0)

Rule-of-thumb: if you need to use INDIRECT for relative references,
there's NEVER a good reason to use A1-style referencing in INDIRECT's
first argument.

"T. Valko" wrote...
That's a generic formula that can be entered anywhere.

Yup. That's the point.

The OP didn't say where the formula would be entered. I don't like using
ROW(), COLUMN() or the equivalent "RC". Depending on where the formula is
entered then you might have to calculate an offset.

The horrors!

OK, not as good as R1C1-style, but still more flexible, if you want
cell B1 in the active worksheet to refer to cell B5 in the workbook
named in cell A1 of the active worksheet, then try the following as
the cell B1 formula.

=INDIRECT("'"&$A$1&'!"&CELL("Address",B5))

Then again, if there were only 12 varying worksheets, you could define
the names

JanWS =Jan!$1:$65536
FebWS =Feb!$1:$65536
MarWS =Mar!$1:$65536
AprWS =Apr!$1:$65536
MayWS =May!$1:$65536
JunWS =Jun!$1:$65536
JulWS =Jul!$1:$65536
AugWS =Aug!$1:$65536
SepWS =Sep!$1:$65536
OctWS =Oct!$1:$65536
NovWS =Nov!$1:$65536
DecWS =Dec!$1:$65536

UseWS
=CHOOSE(MATCH($A$1,
{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";" Sep";"Oct";"Nov";"Dec"},
0),
JanWS,FebWS,MarWS,AprWS,MayWS,JunWS,JulWS,AugWS,Se pWS,OctWS,NovWS,DecWS)

Then use formulas like

=INDEX(UseWS,ROWS(...),COLUMNS(...))

to avoid volatile functions entirely. If there'd be a lot of these
formulas, volatile function calls can take quite a toll on recalc
performance.