Dynamic cell reference within INDIRECT

[itsrich]

I am using this INDIRECT("'"&$A4&"'!"&"$G2:$G300") & INDIRECT("'"&$A4&"'!"&"$G2:$G300") in a formula.

I want the 300 in the range reference to be the value in cell T4. T4 =297

The whole formula (manually changed from above) =IF(COUNTIFS(INDIRECT("'"&$A4&"'!"&"$G2:$G297"),C2,INDIRECT("'"&$A4&"'!"&"$F2:$F297"),C3)=0,"",COUNTIFS(INDIRECT("'"&$A4&"'!"&"$G2:$G297"),C2,INDIRECT("'"&$A4&"'!"&"$F2:$F297"),C3))

One of my goals is to avoid manually changing formulas to replace the actual range on all the sheets within this workbook.

Another goal is to make the range change used in each formula based on the number in column T as I copy the formula down.

I sometimes feel like it is a curse when I am creating such large & involved spreadsheets.

 

[Fluff]

How about

INDIRECT("'"&$A4&"'!"&"$G2:$G"&T4)

 

[itsrich]

I so love this board! TYSVM Fluff.

I work in a place where I am the most knowledgeable Excel person. I know of no other local source for this sort of conversation or knowledge.

Thank You Fluff for opening the door to the next Pandora's Box of Excel knowledge!

 

[Fluff]

You're welcome & thanks for the feedback.