Wrists Hurting!!! There has got to be a better way

[Buzz Local#30]

I need to know if there is any way of copying formulars down the colums but
I dont want the entire formular to change with the repositioning of the cell
WOW that was a mouth full lol

here an example :
2 sheets
sheet 1 has the base info I am calling sheet 2 is collecting the info

sheet 1 Sheet 2
a b c d A B C D
1 1
2 2
3 3
4 4





I have a droplist in 2a sheet 1 that has all the sates in it. In 3d sheet 1
contains an amount
Sheet 2 B1 has a formular SUMIF sheet1 2a , sheet2 A1, sheet1 3d
Sheet2 also has a list of all the states in individual cells so in the
formular above where sheet2 A1 is I want this to change as I go down that
colum but the rest of the formular I dont want to chance
is there a short cut to acheive this?
cause It is taking me an awfully long time to get this done being that I
have fifty states +all of canada to enter this formular in and then again
with diferent cells 3 more times

Sorry if this too drawn out can some one help me?

 

[Alan]

Look at Absolute and Relative referencing in help,
=SUMIF(A1:A20,"aa",B1:B20)
will change the A1:A20 and B1:B20 as you drag it down,
=SUMIF($A$1:$A$20,"aa",$B$1$:$B20) won't
Regards,
Alan.

 

[Tyro]

What is a "formular"?

 

[Gord Dibben]

Check out help on absolute and relative cell referencing.

Note the use of $ signs to fix rows and columns as you drag.


Gord Dibben MS Excel MVP

 

[Klemen25]

And note that to toggle between $ signs in formula you can use F4!

 

[Buzz Local#30]

Hey I tried what you sent to me but I think I am not getting it here what I
wrote
=SUMIF($'Weekly Entry'!$D$10$,C7,$'Weekly Entry'!$P$23:$P24$)
I know that I have placed the $ in the wrong places or havent placed them
where I need them or both which is it?

 

[Alan]

=SUMIF(A1:A20,"aa",B1:B20)
=SUMIF($A$1:$A$20,"aa",$B$1$:$B20)

The dollar signs only go BEFORE the A and the 1. A1 becomes $A$1

If you click the formula and highlight A1:A20 in the formula bar, hit F4 and
the $ signs will be added automatically, do the same with other range.

I didn't use your example as there appears to be something missing in the
first part of it, you only have D10, there should be another cell reference
there.

Regards,
Alan.