Copy cells exactly...

[Gavin Hyde]

Hi,

I'm trying to copy a formula into multiple cells, about 600 in all. The problem that I'm having is that Excel is thinking too much for me. I want to copy the correct formula into another cell as is. When I try and do that Excel is assuming that I want the formula modified.

I had some help from some users here with the formula I'm using (thanks again!), so I'll show the formula as that seems to be the easiest way to illustrate my issue...
=SUMPRODUCT(--(Sheet3!B2:B31=1),--(Sheet3!H2:H31="Bernhoft"))

That is how I need the the formula in every cell in that row with my documents boundries. The only variation that I want is "=1" and "=Bernhoft" but that is on a per row vs. per column basis and I will hand code that.

Assuming that I'm on column 1 and row 1 everything looks good but when I go to column 2 row 1, column 3 row 1 and so on what I get is as follows:

=SUMPRODUCT(--(Sheet3!C2:C31=1),--(Sheet3!I2:I31="Bernhoft"))
then
=SUMPRODUCT(--(Sheet3!D2:D31=1),--(Sheet3!J2:J31="Bernhoft"))
and so on...

I need the column values to stay the same. I have way too many cells involved to change it by hand and I can't figure out how to stop Excel from assuming thats what I want...

Thanks in advance for any assistance,
g

 

[Legacy 63186]

You need an absolute cell reference try.

=SUMPRODUCT(--(Sheet3!$B$2:$B$31=1),--(Sheet3!$H$2:$H$31="Bernhoft"))

 

[Gavin Hyde]

Worked perfectly - Thank you.