Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Taking the last value entered in a column and showing it els

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi there,

    I trying to create a sheet that will take a set of variable data and extract various information from it.

    I have worked out how to get the largest and smallest value from the data =LARGE(A1:A150,1) and =SMALL(A1:A150,1) aswell as the average.

    Yet what I can't seem to do is work out is how to take the last value entered into the data and display it elsewhere.

    For example, using columns.

    Day Data
    1 2
    2 4
    3 9
    4 4
    5 1

    I would like to take the value for day 5 and use it elsewhere but when I enter day 6's data, I would like this then to replace day 5 and so on with each new day's data.

    I looked through the function helps files in Excel XP but could not find any help there.

    I enter the data onto one work sheet but display the calc's on another sheet and that is where I would like the last entered data value shown.

    Is this possible?

    Thanks in advance.

    Guy

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,646
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-16 07:38, GETanner wrote:
    Hi there,

    I trying to create a sheet that will take a set of variable data and extract various information from it.

    I have worked out how to get the largest and smallest value from the data =LARGE(A1:A150,1) and =SMALL(A1:A150,1) aswell as the average.

    Yet what I can't seem to do is work out is how to take the last value entered into the data and display it elsewhere.

    For example, using columns.

    Day Data
    1 2
    2 4
    3 9
    4 4
    5 1

    I would like to take the value for day 5 and use it elsewhere but when I enter day 6's data, I would like this then to replace day 5 and so on with each new day's data.

    I looked through the function helps files in Excel XP but could not find any help there.

    I enter the data onto one work sheet but display the calc's on another sheet and that is where I would like the last entered data value shown.

    Is this possible?

    Thanks in advance.

    Guy
    Guy,

    Lets say that your data are in columns A and B from row 2 on and you want to display in C2 the last value entered in column B.

    =OFFSET($B$2,MATCH(9.99999999999999E+307,B:B)-ROW(2:2),0,1,1)

    will retrieve the desired value.

    Aladin

  3. #3
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    864
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default

    Guy - this is Bill Jelen at MrExcel. Can you go into your profile and change your e-mail address? Somehow the . before the @ is causing everything to be sent to MrExcel.com

    Thanks - and welcome to the board!

    Bill
    View a collection of recent Excel articles in the Excel Daily News

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Guy

    You would be best to create a dymamic range of your data then use:

    =INDEX(MyRange,ROWS(MyRange))

    To see about dynamic ranges I have many examples here: http://www.ozgrid.com/Excel/DynamicRanges.htm

    Or if you do not use a dynamic range you can use:
    =INDEX(A1:A10000,MATCH(-9E+306,A1:A10000,-1))


    Either way, I would avoid using entire columns in formulae as it is bad practice.


  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-16 09:12, MrExcel wrote:
    Guy - this is Bill Jelen at MrExcel. Can you go into your profile and change your e-mail address? Somehow the . before the @ is causing everything to be sent to MrExcel.com

    Thanks - and welcome to the board!

    Bill
    Sorry about that, consider it changed. First time I've ever come across that problem.

    Hope it did not cause too much trouble.

    Guy

    [ This Message was edited by: GETanner on 2002-03-16 18:39 ]

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-16 07:57, Aladin Akyurek wrote:
    On 2002-03-16 07:38, GETanner wrote:
    Hi there,

    I trying to create a sheet that will take a set of variable data and extract various information from it.

    I have worked out how to get the largest and smallest value from the data =LARGE(A1:A150,1) and =SMALL(A1:A150,1) aswell as the average.

    Yet what I can't seem to do is work out is how to take the last value entered into the data and display it elsewhere.

    For example, using columns.

    Day Data
    1 2
    2 4
    3 9
    4 4
    5 1

    I would like to take the value for day 5 and use it elsewhere but when I enter day 6's data, I would like this then to replace day 5 and so on with each new day's data.

    I looked through the function helps files in Excel XP but could not find any help there.

    I enter the data onto one work sheet but display the calc's on another sheet and that is where I would like the last entered data value shown.

    Is this possible?

    Thanks in advance.

    Guy
    Guy,

    Lets say that your data are in columns A and B from row 2 on and you want to display in C2 the last value entered in column B.

    =OFFSET($B$2,MATCH(9.99999999999999E+307,B:B)-ROW(2:2),0,1,1)

    will retrieve the desired value.

    Aladin
    Thank you very much indeed.

    It worked a treat. It did exactly what I needed it to do.

    I can now sit down with a cup of tea and try and work out how it worked.

    Best regards,

    Guy

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-16 13:33, Dave Hawley wrote:
    Hi Guy

    You would be best to create a dymamic range of your data then use:

    =INDEX(MyRange,ROWS(MyRange))

    To see about dynamic ranges I have many examples here: http://www.ozgrid.com/Excel/DynamicRanges.htm

    Or if you do not use a dynamic range you can use:
    =INDEX(A1:A10000,MATCH(-9E+306,A1:A10000,-1))


    Either way, I would avoid using entire columns in formulae as it is bad practice.

    Dave,

    Thanks for the reply. I've not come across the use of dynamic ranges before, so your tip will take me a little more time to apply though I will sit down and have a good go at learning this new topic.

    The answer before yours worked for my current needs but on quick inspection for that link you suggested, I do think my sheet will benefit from using Dynamic Ranges.

    Time to creat another sheet.

    Thanks again,

    Guy

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •