Auto list updating not updating

I've attached a sample. The code works on other workbooks of mine. I started a new workbook and its not automatically update.

You'll notice on my sample I marked in color the last two updates in Column A. You'll notice in Column C the last two entries weren't picked up.

What did I do wrong, I've checked and can't figure it out - any help.

Just copy the formula down two more cells.

NO! The coding should automatically "PICK UP" only the last 20 rows.

Every time a new entry is inputted in Column A Column C should up date itself with only the "last" 20.

The coding I'm using works in other workbooks I have, but now it decides not to work and I don't know nor understand what is wrong.

=IF(ROWS($C$1:$C1)>MATCH(REPT("Z",255),$A$3:$A$2902),"",INDEX($A$3:$A$2902,MATCH(REPT("Z",255),$A$3:$A$2902)-MIN(20,MATCH(REPT("Z",255),$A$3:$A$2902))+ROWS($C$1:$C1)))

Both columns are "general" formatted.

If you keep adding entries in column A nothing happens in column C - it just sits there "struck" on the original 20.

Ah, ok, that's much better information. So, your column C shows the last 20 entries in column A?

Now I see the problem. Your long formula construct is designed to spot the last 20 "text strings" in column A, but the last few entries you put in column A are not text, they are real numbers. Notice the little green flags in that column? That's to warn you that your values are not the same "type", or are text that appear to be numbers but are not.

So, the first thing to do is correct the column so that all values are the same type. I would personally convert all the values to true numbers and apply a custom number format of 000 to show the three-digit display as you have.

Or, you can convert the new entries at the bottom to text by added an apostrophe to the start of the numbers, that would allow you to keep using your long formula construct.

But we can accomplish the same thing with a much shorter formula. In the attached workbook you see the formula for true numeric values in C1, copied down. You also see the formula for all text values in G1 (looking at column E), and also copied down:

=INDEX(A:A, MATCH(99^99,A:A, 1)-19+(ROW(A1)-1))

Put this in C1 and copy down:

custom format does not change what's in cell, if cell was originally a real number say 12 then custom format 000 gives 012 but its still a real number not text "012"

In the workbook I uploaded, I had already converted the "text" numbers in column A to real numbers using a Paste Special trick.

1) Put the number 1 in an empty cell
2) Copy the cell
3) Highlight column A
4) Select Edit > Paste Special > Multilply > OK
5) All the text numbers are now real numbers
6) Apply your custom number format of 000