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

  • Thread starter Thread starter Buzz Local#30
  • Start date Start date
B

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?
 
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.
 
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
 
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?
 
=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.
 
Back
Top