1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by Jerry Calvacca, Feb 8, 2019.

  1. Jerry Calvacca

    Jerry Calvacca New Member

    Messages:
    9
    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

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,150
    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

    Attached Files:

    Last edited: Feb 9, 2019
    Thomas Kuriakose likes this.
  3. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    648
    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, )

    Attached Files:

  4. Jerry Calvacca

    Jerry Calvacca New Member

    Messages:
    9
    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

Share This Page