Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Redmond, Washington, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Should be simple (Excel 2000 SR 1)

    Every 2 weeks I put my vacation and sick leave into a spreadsheet. I then add up the weeks to show total benefit hours available. Each time I do that I have to change the cells in the total formula to the cells at the bottom of the column that contain the latest data. I want to come up with a way to automatically use the last numbers in these columns to make my total benefit hours. This should be simple but I can't seem to master it. Any suggestions?

  2. #2
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Should be simple (Excel 2000 SR 1)

    What I did (and it seemed to work) is put the following function in your Total Benefit Time cell:
    <pre>=(VLOOKUP(TODAY(),A1:G50,4)+VLOOKUP(TODAY(),A 1:G50,7))</pre>

    This will fail if you ever fill more than 50 rows - you can change the range value, though - but should work until then.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Should be simple (Excel 2000 SR 1)

    This should avoid the row limitation:

    =VLOOKUP(MAX(A:A),A:G,4)+VLOOKUP(MAX(A:A),A:G,7)
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    New Lounger
    Join Date
    Jan 2001
    Location
    Redmond, Washington, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Should be simple (Excel 2000 SR 1)

    Thanks to you both. That did it. I should have posted this weeks ago.

Posting Permissions

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