INDIRECT function

[Guest]

Hi
I have been working on this workbook for 12 months or more, it is designed
to help teachers like myself with reporting on student outcomes and it has
become bigger than Ben Hur. This is the last of my challenges with this
workbook.
I have the following formula and would like to refer to a cell reference
described in another cell i.e. E5; E12 and E19 are cell references that I
need to be able to change on occasion and update the formula you see here.
Ideally I would locate a reference to the cells I want to change in the range
D1:E1 [each cell containing a separate cell reference]
Is this possible?

Note: A3=sheet name and B3=class1!C4

The following formula is in E3 and must be able to update automatically. I
have been using the workbook of late and find it a pain when I want to change
the report outcomes by reference.

=(D3&" "&INDIRECT(A3&"!E5")&" "&IF(B3="m","he","she")&"
"&INDIRECT(A3&"!E12")&" "&INDIRECT(""&A3&"!E19")&".")

Kind Regards
Tanya

 

[Bernie Deitrick]

Tanya,

This formula:

=(D3&" "&INDIRECT(A3&"!" & D1)&" "&IF(B3="m","he","she")&"
"&INDIRECT(A3&"!" & E1)&" "&INDIRECT(""&A3&"!" & F1)&".")

Should return the same as your posted formula, if:
the string E5 is in cell D1
the string E12 is in cell E1
the string E19 is in cell F1

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thank you Bernie, you have saved me a lot of time.

My final formula is as follows:

=(D3&" "&INDIRECT(A3&"!"&$E$1)&" "&IF(B3="m","he","she")&"
"&INDIRECT(A3&"!"&$F$1)&" "&INDIRECT(""&A3&"!"&$G$1)&".")

Kind Regards
Tanya

Tanya,

This formula:

=(D3&" "&INDIRECT(A3&"!" & D1)&" "&IF(B3="m","he","she")&"
"&INDIRECT(A3&"!" & E1)&" "&INDIRECT(""&A3&"!" & F1)&".")

Should return the same as your posted formula, if:
the string E5 is in cell D1
the string E12 is in cell E1
the string E19 is in cell F1

HTH,
Bernie
MS Excel MVP

Hi
I have been working on this workbook for 12 months or more, it is designed
to help teachers like myself with reporting on student outcomes and it has
become bigger than Ben Hur. This is the last of my challenges with this
workbook.
I have the following formula and would like to refer to a cell reference
described in another cell i.e. E5; E12 and E19 are cell references that I
need to be able to change on occasion and update the formula you see here.
Ideally I would locate a reference to the cells I want to change in the range
D1:E1 [each cell containing a separate cell reference]
Is this possible?

Note: A3=sheet name and B3=class1!C4

The following formula is in E3 and must be able to update automatically. I
have been using the workbook of late and find it a pain when I want to change
the report outcomes by reference.

=(D3&" "&INDIRECT(A3&"!E5")&" "&IF(B3="m","he","she")&"
"&INDIRECT(A3&"!E12")&" "&INDIRECT(""&A3&"!E19")&".")

Kind Regards
Tanya