Copy formula into multiple cells without changing range

[frankjh19701]

I have a workbook that has many sheets, the first sheet has a lot of
data on it that I want to have in a SUMPRODUCT formula, and I want to
be able to copy that formula into multiple cells of the other sheets
without changing the range the formula refers to. Example:
=SUMPRODUCT(--('All History'!$B2:$B2000=1),--('All
History'!$C2:$C2000=2))

This formula is in one cell, but I want to have it copied to
multiple others. But, when I try to copy it, the range changes, i.e.
$B2 will become $B3 and so on.
I also need to be able to change the value I'm looking for without
changing the rest of the formula, i.e. change the =1 value to =2, and
so on. Any ideas?

 

[Peo Sjoblom]

Use

$B$2:$B$2000


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[MartinW]

For the second part of your question you could use a helper
column (say column G) with the numbers 1 to 20 or whatever
descending down the column.

Your formula will then be
=SUMPRODUCT(--('All History'!$B$2:$B$2000=$G1)

As you copy down the $G1 reference will update to $G2, $G3 etc.

HTH
Martin

 

[frankjh19701]

For the second part of your question you could use a helper
column (say column G) with the numbers 1 to 20 or whatever
descending down the column.

Your formula will then be
=SUMPRODUCT(--('All History'!$B$2:$B$2000=$G1)

As you copy down the $G1 reference will update to $G2, $G3 etc.

HTH
Martin

Thank you for the help, however, it doesn't completely work. I totally
forgot about using the $ to state that I want the constant in a
formula, but I still can't copy the formula without A)Somehow altering
the original formula and B)the formula doesn't stay the same or work
after pasting it into a different cell. Let me explain:
I have a formula that I've pieced together that is :
=SUMPRODUCT(--(All!$B$2:$B$2000=All!$M3),--(All!$C$2:$C$2000=All!$M4))+SUMPRODUCT(--(All!$B$2:$B$2000=All!$M3)--(All!$D$2:$D$2000=All!$M4))+SUMPRODUCT(--(All!$B$2:$B$2000=All!$M3),--(All!$E$2:$E$2000=All!$M4))+SUMPRODUCT(--(All!$B$2:$B$2000=All!$M3),--(All!$F$2:$F$2000=All!$M4))+SUMPRODUCT(--(All!$C$2:$C$2000=All!$M3)--(All!$D$2:$D$2000=All!$M4))+SUMPRODUCT(--(All!$C$2:$C$2000=All!$M3),--(All!$E$2:$E$2000=All!$M4))+SUMPRODUCT(--(All!$C$2:$C$2000=All!$M3),--(All!$F2:$F$2000=All!$M4))+SUMPRODUCT(--(All!$D$2:$D$2000=All!$M3)--(All!$E$2:$E$2000=All!$M4))+SUMPRODUCT(--(All!$D$2:$D$2000=All!$M3),--(All!$F2:$F$2000=All!$M4))+SUMPRODUCT(--(All!$E$2:$E$2000=All!$M3)--(All!$F$2:$F$2000=All!$M4))

"All" has been changed from "All History" due to the length of the
formula (I couldn't complete the formula without getting an error
message about it being too long). And I have been analyzing data and
comparing it to repeating sequences across columns (But, in the same
row). Hence why I have the formula refering to Column B to Column C,
then Column B to Column D, and so forth.

Now, when I try to copy the formula, it alters the formula and what
value the cell reflects is #######. Even if all I do is click on the
cell and then just click in the formula bar (Not changing/typing
anything) just clicking in the formula bar!

I took the advice of making a "Helper Column", which is in the sheet
All and in the column M, but the rest of this is all a mystery to me.
How can the formula change when I'm not doing anything to it but
clicking in the formula bar? And how can I copy this formula into other
cells without changing it? I really need some help on this.

 

[frankjh19701]

For the second part of your question you could use a helper
column (say column G) with the numbers 1 to 20 or whatever
descending down the column.

Your formula will then be
=SUMPRODUCT(--('All History'!$B$2:$B$2000=$G1)

As you copy down the $G1 reference will update to $G2, $G3 etc.

HTH
Martin

Thank you for the help, but I now have a new problem: the helper column
seems to stop "helping" after the first dozen numbers or so, Why is
this?
The first couple of lines work on the first few sheets in the workbook,
but then it seems to stop - all of my formulas only produce zeros. It
shouldn't produce zeros especially when there is data that I know for a
fact should be there. What am I doing wrong? I have a workbook with
aprox. 50 pages and they all reference the first in their formulas. If
you need more info, please let me know. Since there is SO much data and
it is sensitive, I don't want to try to post it here. I look forward to
your help.