Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Only display the last entry in a range

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

    Default

    I have a row of data B3:G3 and a summary cell H3.

    Cell B3 C3 D3 E3 F3 G3
    Value 12

    in H3 this would display 12

    If I then entered 14 in D3 it would then show 14 in H3.

    Any suggestions?

    Thanks

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What about:

    =MAX(B3:G3)

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

    Default

    On 2002-03-01 04:04, Ian Mac wrote:
    What about:

    =MAX(B3:G3)
    Ian,

    Not sure but it seems Bobefax wants in H3 the value of the cell changed most recently in the range B3:G3. That would require VBA, I think.

    Aladin

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry,

    I should have been clearer your suggestion only shows the highest value.

    I need to show any value that is typed after B3 regardless of value, so I am looking to change the value of H3 everytime a subsequnat entry is made in the ros B3:H3.

    Thanks anyway.

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

    Default

    Ian/Aladin,

    Don't think I need VBA (hope not anyways!)

    It sounds simple, just to change the value in H3 to match a value in range B3:G3, the values of range B3:G3 will be entered over a period of time (weekly), each Column B-G is a time period, so once entered they will not be changed.

    At the end of the period G3 this will be the value in H3, but say after 3 weeks the value of D3 would need to be displayed 8 in H3.

    Hope this clears it a bit more!.

    Thanks


    [ This Message was edited by: Bobefax on 2002-03-01 04:46 ]

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

    Default

    On 2002-03-01 04:45, Bobefax wrote:
    Ian/Aladin,

    Don't think I need VBA (hope not anyways!)

    It sounds simple, just to change the value in H3 to match a value in range B3:G3, the values of range B3:G3 will be entered over a period of time (weekly), each Column B-G is a time period, so once entered they will not be changed.

    At the end of the period G3 this will be the value in H3, but say after 3 weeks the value of D3 would need to be displayed 8 in H3.

    Hope this clears it a bit more!.

    Thanks


    [ This Message was edited by: Bobefax on 2002-03-01 04:46 ]
    If you have something like:

    Week 1, a value is entered in B3, say 8, C3:G3 is empty as yet;

    Week 2, a value is entered in C3, say 5, D3:G3 is empty as yet;

    etc., then

    H3 will have the value of B3 in Week 1, that is, 8;

    H3 will have the value of B3 in Week 2, that is, 5;

    etc.

    Then that's doable with a formula in H3, but I'm not yet convinced that this is the case.

    Any comments?

    Aladin

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

    Default

    Aladin,

    What you have described id EXACTLY what I want.

    HELP..........

    Bobefax

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

    Default

    On 2002-03-01 06:02, Bobefax wrote:
    Aladin,

    What you have described id EXACTLY what I want.

    HELP..........

    Bobefax
    In H3 array-enter:

    =INDEX(3:3,SUM((MAX((LEN(B3:G3)>0)*COLUMN(B3:G3)))))

    To array-enter a formula you need to hit control+shift+enter, not just enter.

    Aladin


    [ This Message was edited by: Aladin Akyurek on 2002-03-01 06:32 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Boston, Mass
    Posts
    182
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,
    Would the idex/match function work just as well? Just wondering.

    =INDEX(B3:G3,MATCH 9.99999999999999E+307,B3:G3))

    Drew

  10. #10
    New Member
    Join Date
    Feb 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Drew,

    Having a problem with your suggestion.

    =INDEX(B3:G3,MATCH 9.99999999999999E+307,B3:G3))

    Seems it may not have pasted across OK.

    Only one open bracket but two close !!

    bobefax

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
  •