Indirect with relative reference

[abhi2611]

Hi,

I have an indirect formula that I use.

SUMIF((INDIRECT($A4&"!$G$259:$G$558")),$D4,(INDIRECT($A4&"!CQ$259:CQ$558")))

Is there are way to have the second half of the formula to move relative with the column...

In other words, I need the CQ to change to CR,CS,CT..so on.

If I have the column names in a cells, is there a way I could just refer it to that cell and drag it sideways?

Any help is appreciated.

Thank you,
A.G.

 

[Somendra Misra]

Hi A.G.,

Can you upload a sample file.

Regards,

 

[KenU]

@abhi2611,

Try the change below in which I used B$1 as the anchor range in which you begin listing the columns. You can change B$1 to the appropriate starting cell address.

SUMIF((INDIRECT($A4&"!$G$259:$G$558")),$D4,(INDIRECT($A4&"!" & B$1 & "$259:" & B$1 & "$558")))

Hope that helps.

Regards,
Ken

 

[Luke M]

We can go another route. We'll use INDIRECT to build a larger array, and then use INDEX to choose the column we are interested in. Then, the COLUMN function will increment as we copy to the right, causing us to get a different column via the INDEX/INDIRECT.

=SUMIF((INDIRECT($A4&"!$G$259:$G$558")),$D4,INDEX(INDIRECT($A4&"!CQ$259:CZ$558"),,COLUMN(A$1)))

 

[abhi2611]

Thank you guys for your input. This was exactly what I was looking for.