Copy cells exactly...

Gavin Hyde

New Member
Joined
Jul 18, 2002
Messages
19
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
L

Legacy 63186

Guest
You need an absolute cell reference try.

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

Forum statistics

Threads
1,141,152
Messages
5,704,600
Members
421,359
Latest member
Edwardvanschothorst

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top