Summing rows of varying length

Posted by Stephen Giles on June 15, 2001 8:59 AM

I have two sheets in an excel 97 file called overtime and YTD. YTD contains a master list of all employees' names in column B together with corresponding staff numbers in column C. Column E is headed Overtime. The Overtime sheet contains identical information in columns B and C, but only for those employees who have been paid overtime, and not in the same order. Columns D to O are headed months of the year and payments of overtime have been entered in the cell relating to employee and month. I need to have on the YTD sheet in column E, a year to date total of the overtime for each employee, the year to date month being entered in B2 on YTD sheet. So if I enter September in that cell, the the figures relating to an employee number on the Overtime sheet will be added in the January to September columns. I feel this ought to be achieved by a cunning combination of VLOOKUP and OFFSET but no luck so far. Any suggestions would be welcome.

Stephen

Posted by IML on June 15, 2001 10:32 AM

One idea

Here is an idea, not sure if it will work.
First use a real date in YTD!B2 and format it to show your month (mmmm)
Second, add up the year to date figure on the overtime sheet in column P with
=SUM(D3:(INDIRECT(ADDRESS(ROW(),MONTH(YTD!$B$2)+3))))
Now just use vlookup on the employee number and capture the number in your column P.

good luck.

Posted by Stephen Giles on June 16, 2001 1:59 AM

Re: One idea

Thanks for this, I'll give it a try when I've done the shopping (while my dear wife teaches) It's 10am London time here!!!