Excel 2003 data input and transfer it to another field which scrolls upwards to keep the last input and previous inputs

Question

Here is the goal-

I have a table of data that I am trying to populate. The spreadsheet is financial in nature and it tracks portfolio values as the user inputs them. It is a trailing 18 month data sheet.

The most recent data is that the bottom of the list.

I am trying to create a field where a user can enter a month/dollar value and it will populate at the bottom of the list (the most recent entry) while pushing older data upwards and dropping of the 19th month. Example- this month is Feb. The user would put Feb in the box, it will move the last entry (Jan) up one cell and drop off the the last entry (sep). By entering in Feb, the user then pushes all data upwards one cell, preserving the integrity and order of the list the user created over time.

• a1-sep -oldest entry drops off-
• a2-oct
• a3-nov
• a4-dec
• a5-jan
• a6-feb
• a7-mar
• a8-apr
• a9-may
• a10-jun
• a11-jul
• a12-aug
• a13-sep
• a14-oct
• a15-nov
• a16-dec
• a17-jan

feb is entered into a cell and populated in A17 while pushing all data upwards and dropping off sep maintaining the 18 month list.

I need it to also keep the formulas attached to the cells. I have it so numbers will return certain values when the cell is populated. I just want the data scrolled, not the cells.

Help appreciated

JC

Answer 1

Hi JC,

I believe that the best way to do this is store the data being entered in another range, and then just use formulas (eg: INDEX, SUMIF) to return the most recent 18 numbers to your a1:a17 range. If this needs to look transparent you can put the main data on a different (hidden) sheet.

This gives you the advantage of having the entire data history available in the sheet, but only working with the most recent 18 entries.

Russ