INDIRECT in SUMIF formula - Relative reference problem (Excel 2007)

[Pennimus]

Hi all,

The following formula works to =sumif certain values from another worksheet when A3 is the name of the worksheet.

=SUMIF(INDIRECT("'"&$A3&"'!$A:$A"),"Churned",INDIRECT("'"&$A3&"'!Q:Q"))

When I drag this formula up or down my sheet, the row number in the reference to A3 changes as expected.

However, when I drag it right or left, the column reference Q:Q doesn't change, despite being a relative reference

Does anyone know how (or if) it's possible to make the reference to column Q change as normal in this formula?

Thanks very much for your help.

 

[GlennUK]

Q:Q isn't a reference, it's part of a string, so no wonder it doesn't change when you copy.

This may do what you want:
=SUMIF(INDIRECT("'"&$A3&"'!$A:$A"),"Churned",OFFSET(INDIRECT("'"&$A3&"'!A:A"),0,COLUMN()))
although you may have to add or subtract from COLUMN() depending on the location of the formula relative to column Q.

 

[jasonb75]

You could also reference column Q in R1C1 relative to the cell holding the formula, removing the need for extra functions.

For example, if your formula is going in column G

=SUMIF(INDIRECT("'"&$A3&"'!$A:$A"),"Churned",INDIRECT("'"&$A3&"'!C[10]",0))

Column Q is 10 to the right of column G.

 

[Pennimus]

Thank you gentlemen.

Both solutions work a treat.