Auto Increment INDIRECT Column Reference

[dwgnome]

The formula below sums across worksheets:

[COLOR="Navy"]=[SIZE="2"]SUMPRODUCT(SUMIF(INDIRECT("'"&T11:T18&"'!CE35"),">0"))[/SIZE][/COLOR]

Where T11:T18 houses the worksheet names. The formula works fine.

How can I get the CE35 to increment by one column to the right as I drag the formula to the right. That is CF35, CG35 etc.

Your assistance is appreciated.

 

[T. Valko]

Try this...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$T11:$T18&"'!"&ADDRESS(ROW($CE35),COLUMN(CE35))),">0"))

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$T$11:$T$18&"'!"&CELL("address",CE35)),">0"))

 

[dwgnome]

Both Aladin and #NAME's formulas work perfectly. Thank you gentlemen.

What I find amazing is that CELL (with "address" info type) acts like an increment source - similar to ROW or COLUMN. Yet could not have gathered that from Microsoft Help. It simply states "Returns information about the formatting, location, or contents of the upper-left cell in a reference."

Again thank you for that insight.