Adding a single date and number to a max list of 20

[khank]

I'm looking to input only a single date and number and have that information automatically update a list of 20 (dropping off the oldest entry).

I know the below coding will bring in the last 20 entries of a list:

=IF(ROWS($AC$95:$AC95)>MATCH(REPT("Z",255),$C$4:$C$2902),"",INDEX($C$4:$C$2902,MATCH(REPT("Z",255),$C$4:$C$2902)-MIN(20,MATCH(REPT("Z",255),$C$4:$C$2902))+ROWS($AC$95:$AC95)))

Is it possible to record a single date and number in Columns A1 & B1, then in Columns F & G update automatically to a max amount of 20 entries?

See sample, I marked in yellow the last entry.

[NBVC]

I am not sure I understand this one.

Are you starting with the list of 20 entries and removing one date at a time?

Can you please explain further what are the givens and what exactly is supposed to happen as you enter new dates and codes.

[khank]

No starting with entering the latest date and number in Columns A1 & B1.

Now you've got me thinking - or do I just have a single column list of 20 rows updating daily (A & B)?

[NBVC]

So what's next? I am not sure of the status.

[khank]

Sorry for not explaining myself more clearly.

What I'm wanting to do is add today's date and number my list of 20. The list "cycles" automatically - adding the new date and number, and dropping off the oldest date and number, so I always have a max of 20 rows in my list.

[NBVC]

So where are the numbers in column G coming from? How do those cycle?

As far as the date is concerned, do you mean?

In F1:

=A1-19

and in F2 enter =F1+1 copied down to F20.

[khank]

You notice on the new sample I changed your coding slightly.

Now in A1 I put =TODAY() coding and your coding underneath will the list "move" down automatically, including the number when there's a new day? Just trying to understand.

[NBVC]

That works.... but I don't know about the codes in column B and how those should/would move around.

[khank]

A1 & B1 are the latest entry. With each new entry the list (date and number) automatically moves down one row. I hope that makes sense.

[NBVC]

I think I understand now, but I also think the only way to accomplish that will be with VBA and I am not sure exactly how, though at the moment.

[khank]

And I'm on OS X Excel - I know nothing about Visual Basic. Oh, well we gave it the old "college-try". Maybe there's a way to alter the coding I'm using to pick up the last 20 entries from a 1200+ list to just pickup the latest entry and index the past 19 entries. If that makes any sense.

[NBVC]

That might be a possibility. You'd have to post another workbook showing a sample of that and what you'd expect

[khank]

I've attached a new workbook.

Remember the new entry (A1 date & B1 number) is only for today - and that will stay until tomorrows' date, that entry then moves down the list leaving today's date in A1 and blank B1 (waiting for a new number to be entered). There only be a total of 20 entries on the final list.

[NBVC]

This is not really different than what you had before.

I was referring to your statement:

Maybe there's a way to alter the coding I'm using to pick up the last 20 entries from a 1200+ list to just pickup the latest entry and index the past 19 entries

What does that list of 1200+ look like? Where is it? What is the logic on what to pick up and when?

[khank]

I've attached a sampling of numbers from the 1200+ list.

This is the "OLD" way I've been doing the entries.

I've realized with new direction I didn't require all those numbers (as the file gets bloated slowing down the calculating speed).

After thinking about the problem I came to the conclusion I only required twenty entries - thus reducing necessary worksheets and the overall file size.

As you see, listed on the attached Workbook34, the latest entry is at the bottom. Then H16:H35 would pickup the last 20 numbers.

I want to rid myself of those 1200+ list or "shrink" it down to only a maximum of 20 entries.

That is the reason in Workbook33, I was thinking A1:B1 would be only a "helper" column for the new date and number. And some how that entry could be picked up and placed as the latest entry in the list of 20 - which updates with each new entry.

And how could I get (example: Workbook34 H16:H35) to do the same thing in a new document.

[NBVC]

But like I said, without VBA how would you automatically push everything down by one row each time you entered a new date and preserve the values to the next rows and then eliminate the last row to keep the circulating 20 entries.... besides I don't even think your Mac version has VBA capability anyway.

One other possiblity is that you insert a new row 1 each day with new date and code, then you can apply conditional formatting to columns A and B with formula:
=ROW()>20 and format to change font to white (to match background)... this will make it appear as though you always have the 20 rows, but in reality they are all pushed down and are just "made invisible". Would that work for you?

[khank]

That right there's no VBA on the Mac.

Good thought, but no - might as well keep the original list of entries. I'll close out this thread as "SOLVED". Thanks for all your help and thoughts.