drag or copy formula as dynamic formula

[cartja]

This is probably a simple question but I'm puzzled by the fact that this formula won't drag/copy as a dynamic formula should.. it remains stagnant as if i were useing $ on column and row refs. In this example, row 22 is the only specific reference.

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!J$22"),"=1",INDIRECT("'"&list&"'!J6")))

Don't know if it has to do with the list reference... If any of you know, please make it known to me.

Thanks!

j-bob
excel 2077

 

[Jonmo1]

Welcome to the board...

This is because "!K$22" and "!K4" are TEXT strings, not ranges.
So they do not incriment.

Do you want them both to incriment, or only the K4
I assume only the K4 because you put the $ on K$22

Try

=SUMPRODUCT(SUMIF(INDIRECT(""&list&"!K$22"),"=1",INDIRECT(""&list&"!K"&Row(K4))))

 

[cartja]

Thank you jonmo1! That works!

How do you combine row and column dynamics?

I tried: &column(k5)&row(k5) but it didn't work.. Suggestions?

thanks!
j

 

[Jonmo1]

that get's more tricky, because the column function returns a # not a Letter.
You need to use the Address(row#, Col#) function..

Try this again only doing the K4 reference..

=SUMPRODUCT(SUMIF(INDIRECT(""&list&"!K$22"),"=1",INDIRECT(""&list&"!"&ADDRESS(COLUMN(K4),ROW(K4)))))