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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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"))
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,536
Members
410,547
Latest member
htran4
Top