• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Return values of cells in the row above the last entry

Jerry Calvacca

New Member
Hello,

Please see the attached file:

Log is where the data will be entered...
-1 City is where it will always return the last entry in the log
-2 City is where it will return the last entry AND the one above it (as data will be always entered in order)
-3 city returns last 3 lines of data, 4 city will return last 4 lines of date, etc...

Order is not important on the multiple line tabs, just that it includes the correct number of lines of data.

Thanks for the help in advance!!

Jerry
 

Attachments

bosco_yip

Excel Ninja
1] Create a Data Validation dropdown number list {1,2,3,4,5} in C1.

2] In A3, formula copied across and down :

=INDEX(Log!B:B,MATCH(9.9E+307,Log!$B:$B)-$C$1+ROWS($1:1))&""

3] Select C1 and choose the number from the dropdown list for testing
purpose

Regards
Bosco
 

Attachments

Last edited:

Peter Bartholomew

Well-Known Member
A glimpse of the future?
Assuming you have the input data in a table in order to provide a meaningful way of addressing the data, then the number of rows N is given by
N: = ROWS(Table1)
A countdown index 'k' can then be defined to refer to the formula
k: =SEQUENCE(N,1,N,-1)
The M rows to be returned from the table are given by the formula
= FILTER(Table1, k<=M)
entered into one cell.

It is possible to do without the intermediate named formulae but I think the form
= FILTER( Table1, SEQUENCE(ROWS(Table1), 1, ROWS(Table1), -1) <= M )
lacks the clarity of the previous version. It has too great a focus on the trivial mechanics of the calculation which are best kept out of sight.

To return the final record only it is probably easier to stick with INDEX
= INDEX( Table1, N, )
 

Attachments

Jerry Calvacca

New Member
Hi Bosco,

Thanks for the information it worked perfectly.
Would there be a way with your functionality if I wanted to manually search for an entry that is in the "middle" of the log and then determine if there are 1,2,3,etc entries, and then return the results of the search?
This is assumed to be on a separate tab than the one that returns the last lines of the log (with data valadation etc.)

I appreciate the help in this project!

Jerry
 
Top