Auto Fill

C

Connie Martin

A177 has a number. B177 has this formula: =COUNTIF($A$2:$A177,$A177)
I've had to drag that formula down now for 177 rows. Why will it not
automatically complete once I enter the number in Col. A.

G177 has a number. H177 has this formula:
=IF(G177="","",IF(G177<4,"LD","SU"))
As soon as I enter a number in G177, H177 completes automatically. I never
have to drag the formula down.

Why won't the Col. B formula work auto-fill like the Col. H formula does?

Connie
 
E

Elkar

Since the range in your first formula is growing everytime it is copied down
a cell, Excel doesn't recognize the formula as being consistent and therefore
won't auto-populate new rows.

You could trick Excel by re-writing your formula in a consistent manner, like:

=COUNTIF(INDIRECT("$A$2:A"&ROW()),A2)

WIth the use of INDIRECT and ROW, the formula really isn't changing when
copied, and Excel will auto-populate it.

HTH
Elkar
 
C

Connie Martin

Thank you! That works. I'll use that formula because I find it annoying to
have to stop and drag down a formula. I understand the concept, but would've
never figured out. Thankfully there are gurus around like you!! :) Thank
you! Connie
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top