INDIRECT function in Office 2007

[E]

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

 

[Luke M]

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.

 

[Harlan Grove]

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

 

[T. Valko]

Try it like this:

A1 = Jan (as a TEXT entry)

=INDIRECT(A1&"!B5")

Which evaluates to: =Jan!B5

 

[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.

 

[E]

Just worked it out, using ROW. Thanks.

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.

 

[T. Valko]

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

 

[Harlan Grove]

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]

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.

 

[Harlan Grove]

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,SepWS,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.