Auto Fill
 

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

Auto Fill
 

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


"Connie Martin" wrote:

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

Auto Fill
 

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

"Elkar" wrote:

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


"Connie Martin" wrote:

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