copy formula

[cunger]

hi
Can anybody help me to figure out how to handle the following problem:
I wanna copy this formula along a column:
=SUMPRODUCT(M$3:M$4500,'Raw Data'!$F$4:$F$4501)
I thougt that Excel change the M to N,O,P,.. by itself wenn I pull the formula down, but this is not the case.
Thanks for your time and help.
Chris

[Lotus123]

Excel would only update the M,N,O,P by copying the formula to the right (you changed columns). When you copy up and down Excel would normally change the row numbers; however, you have $'s in front of them so they won't change.

[Lotus123]

Ooops...hit send a little early. That all being said, you could use this formula:

=SUMPRODUCT(INDIRECT(CHAR(76+CELL("row",A1))&"3:"&CHAR(76+CELL("row",A1))&"4500"),'Raw Data'!$F$4:$F$4501)

When you copy that formula down it will update from M to N, O, P, etc.

I actually had an interesting time making the above formula...for some reason row() doesn't work inside INDIRECT() in the way I was trying to use it. Maybe someone else can explain that one (it looks like Excel kept converting everything to an array before passing to INDIRECT() and it would subsequently return a #VALUE! error).

[cunger]

Thanks for your formula,
but I'm new with Excel stuff. Can you explain me the function you make with "row" and char(67+Cell), please.

Your formula starts at A1 and goes down the column. I need something that goes with a the column itself for instance K3:K4500.

I try to change the the formula but like I say. I start just right now to get confirm with Excel.Thanks for your help