excel help

[rattlsnak]

Who knows Excel? I have a workbook with two sheets. Sheet 1 has multiple rows ans two columns with their name in one and a certain value in the other column. (A1, A2, AA1, etc). On sheet two, I have the peoples names again and am trying to count the values off of sheet one and have them displayed on sheet 2. (like how many A1, A2, etc did this person receive on sheet 1).

I know how to make the formula to do this, but is there a faster way to simply "slide" the formula to all of the rows/columns? Every time I try to simply drag the formula to the next cell it is adding to the wrong value.

As an example, my formula for my cell that i want the data to go to is: =COUNTIF('Sheet 1 '!F:F,D1) but every time I drag it to the next cell down so it will auto populate, it will go to:
=COUNTIF('Form Responses '!F:F,D2) but i want to go to :
=COUNTIF('Form Responses '!H:H,D1)

and if I go to the next cell to the right, it will go:
=COUNTIF('Form Responses '!F:F,D2) and I want it to go to:
=COUNTIF('Form Responses '!F:F,E1)

So its counting up on the wrong value if that makes sense. Is there any way to make it continue on what it thinks is an inconsistent formula so I dont have to type out 1,000 of these??!!

[masraum]

Originally Posted by rattlsnak

Who knows Excel? I have a workbook with two sheets. Sheet 1 has multiple rows ans two columns with their name in one and a certain value in the other column. (A1, A2, AA1, etc). On sheet two, I have the peoples names again and am trying to count the values off of sheet one and have them displayed on sheet 2. (like how many A1, A2, etc did this person receive on sheet 1).

I know how to make the formula to do this, but is there a faster way to simply "slide" the formula to all of the rows/columns? Every time I try to simply drag the formula to the next cell it is adding to the wrong value.

As an example, my formula for my cell that i want the data to go to is: =COUNTIF('Sheet 1 '!F:F,D1) but every time I drag it to the next cell down so it will auto populate, it will go to:
=COUNTIF('Form Responses '!F:F,D2) but i want to go to :
=COUNTIF('Form Responses '!H:H,D1)

and if I go to the next cell to the right, it will go:
=COUNTIF('Form Responses '!F:F,D2) and I want it to go to:
=COUNTIF('Form Responses '!F:F,E1)

So its counting up on the wrong value if that makes sense. Is there any way to make it continue on what it thinks is an inconsistent formula so I dont have to type out 1,000 of these??!!

Put a $ in front of any reference that you don't want to change (anchor your reference).

IE, if you need =COUNTIF('Sheet 1 '!F:F,D1) to keep the "D1" then go with $D$1. If you only wanted one of those to stay (for instance, you always want to stay with the 1st row, but you want it to change columns, then you'd use D$1. On, if you want tho column to stay the same, but the row needs to change, then $D1.

[rattlsnak]

Omg... that worked! I had to do =COUNTIF('Form Responses '!$F:$F,D1) to get it to do what i needed. Let me know if that looks funky, but it works!

Thank you!!!!

[masraum]

Originally Posted by rattlsnak

Omg... that worked! I had to do =COUNTIF('Form Responses '!$F:$F,D1) to get it to do what i needed. Let me know if that looks funky, but it works!

Thank you!!!!

No, it doesn't look funky to me. Anywhere that you need to anchor a reference, the $ is what you need, at least, in my experience.

If you just need to get a count per person in a list, then a pivot table might be the fastest way to do that, but without knowing exactly what you're working with and what you need, I can't say for sure. If you've gotten this to work and it's giving you what you need, then that's perfect. And I am far from an excel expert so I could be wrong.