Should be simple (Excel 2000 SR 1)

Topic

New Reply

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?

Reply | Quote

Replies

What I did (and it seemed to work) is put the following function in your Total Benefit Time cell:

=(VLOOKUP(TODAY(),A1:G50,4)+VLOOKUP(TODAY(),A1:G50,7))

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

Reply | Quote

This should avoid the row limitation:

=VLOOKUP(MAX(A:A),A:G,4)+VLOOKUP(MAX(A:A),A:G,7)

Reply | Quote

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

Reply | Quote