Indirect function within an indirect function

[wjbottiny]

=INDIRECT("'"&K$4&"'!"& INDIRECT("'"&K$2&"'!"&"R2"&14):INDIRECT("'"&K$2&"'!"&"S2"&14))

Cell k4 contains sheet name 1
Cell K2 contains sheet name 2

Cells r2 and s2 in sheet name 2 contain letters representing column references, for row 14

I can get the two indirect functions to work separately, but when I combine them into the above formula, I get #value!

what is wrong with the formula?

Thanks


System Info Sheet in workbook

Annual Sheet in workbook


Method 1 2950 summing three cells in Annual sheet directly

Method 2 2950 summing those cells in this sheet by way of indirect reference to System Info sheet

Method 3 2950 summing three cells in Annual sheet by way of indirect reference to Annual sheet


832 982 1135
#VALUE! trying to sum those three cells by way of indirect reference to Annual sheet and indirect reference to System Info sheet (combining Methods 2 and 3)

 

[wigi]

I can get the two indirect functions to work separately

I doubt you can. Or at least it's very unclear here.

What should this give us:

INDIRECT("'"&K$2&"'!"&"R2"&14)

and how is it set up / what are the components?

By the way, looking at these kind of functions that you have there, I really think that a bad spreadsheet design is the basis.
Before creating a spreadsheet, think about its structure, sheets, calculations, and so on. Only after that, create the spreadsheet. Not the other way around.

 

[vogel997]

=SUM(INDIRECT("'"&K4&"'!"&INDIRECT("'"&K2&"'!R2")&"14"),INDIRECT("'"&K2&"'!"&INDIRECT("'"&K2&"'!s2")&"14"))

This should work, try it out

<colgroup><col width="965" style="width: 724pt; mso-width-source: userset; mso-width-alt: 35291;"> <tbody> </tbody>

 

[billszysz]

=indirect("'"&k$4&"'!"& indirect("'"&k$2&"'!"&"r2"&14)&":"&indirect("'"&k$2&"'!"&"s2"&14))
or maybe
=INDIRECT("'"&K$4&"'!"& INDIRECT("'"&K$2&"'!"&"R2")&"14:"&INDIRECT("'"&K$2&"'!"&"S2")&14)

 

[billszysz]

Or
=INDIRECT("'"&K$4&"'!"& INDIRECT("'"&K$2&"'!R2")&"14:"&INDIRECT("'"&K$2&"'!S2")&14)

 

[markmzz]

Try this small modification in the formulas posted too:

=SUM(INDIRECT("'"&K$4&"'!"&INDIRECT("'"&K$2&"'!"&CELL("address",R2))&"14:"&INDIRECT("'"&K$2&"'!"&CELL("address",S2))&14))

Markmzz

 

[wjbottiny]

perfect. Thanks.