Moving Array

[Guest]

I have a table, 6 columns wide, updated biweekly. By updating, I mean that another set of 6 values is entered in the first available row beneath all previously entered values. I have a set of formulas, graciously provided by a user on this forum, that search in a smaller array for most frequent values. I need the array reference portion of this formula to reference only the 50 most recent entries in the bottom of this 6 column array
The cells in Column A have a simple formula to number each new entry with a value 1 higher than the previous entry. The 6 key columns begin with Column B and over. The reason for the numbering is that the array does not start in Row 1, rather it starts in Row 5. So, the 1st entry would be B5, the 41st would be B45, and so on.
Say, for example that Entry # 600 is the last set entered, "Row 604", and I want to reference Entries 551-600 in the formula. That I can do, however, when I make 5 more entries, I need the formula to compensate and track Entries 556-605 instead. How do I do this

Thanks
Jeremy N.

 

[Guest]

Hi Jeremy
The following array entered formula should do the trick

=OFFSET($A$1,MAX((A1:A1000<>"")*ROW(A1:A1000))-50,1,50,6

Use ctrl-shift-enter instead of enter when you input the formula

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- LupusMan wrote: ----

I have a table, 6 columns wide, updated biweekly. By updating, I mean that another set of 6 values is entered in the first available row beneath all previously entered values. I have a set of formulas, graciously provided by a user on this forum, that search in a smaller array for most frequent values. I need the array reference portion of this formula to reference only the 50 most recent entries in the bottom of this 6 column array
The cells in Column A have a simple formula to number each new entry with a value 1 higher than the previous entry. The 6 key columns begin with Column B and over. The reason for the numbering is that the array does not start in Row 1, rather it starts in Row 5. So, the 1st entry would be B5, the 41st would be B45, and so on.
Say, for example that Entry # 600 is the last set entered, "Row 604", and I want to reference Entries 551-600 in the formula. That I can do, however, when I make 5 more entries, I need the formula to compensate and track Entries 556-605 instead. How do I do this

Thanks
Jeremy N.