Excel Indirect function ; refer to call rather than "hard code" reference

[dicken]

Hi,

In the following formula I have type in the "A2:A6";

SUMIF(INDIRECT("'"&Sheets&"'!"&"A2:A6"),"a",INDIRECT("'"&Sheets&"'!"&"B2:B6"))

Sheet = named range

is there a way to have the reference to A2 to A6 in other cells so a cell A1 = "A1", A2 = "A6" and then just select those two cells, I've tried this and it does
not seem to work, I've also entered all the values as a range a1, a2, a3, a4, a5, a6, I also tried to the indirect just for the sheet
INDIRECT( Sheet&"!") & Range , but this did not work either?

 

[dreid1011]

Have you tried this?

INDIRECT("'"&Sheets&"'!"&A1&":"&A2)

 

[dicken]

Have you tried this?

INDIRECT("'"&Sheets&"'!"&A1&":"&A2)

Thanks, no I hadn't and yes it works.