Microsoft Excel 2000

Hi. I have a system where in column a there are dates for 1 month. In column D there is for every day the total worked time and in E the total earned wage overthis date. Somewhere next to it i want to be able to type any two dates and so that all the values between and including these dates are included. Both dates are from the existing dates in the table.

Thanks

I think you could use a Dynamic Range Name to do this. I tried this and it seems to work. Assumptions:

Dates are in A1:A31

Hours are in B1:B31

The first date you want to match is in C1

The last date you want to match is in D1Pull down Insert...Name...Define

In the

Names In Workbookbox enter MyHours

In theRefers tobox enter this formula:=OFFSET($B$1, MATCH($C$1,$A1:$A$31)-1, 0,(MATCH($D$1,$A1:$A$31)-MATCH($C$1,$A1:$A$31)+1))

To use this Dynamic Range Name, enter

=SUM(MyHours)in a cell.By using the formula given, the range MyHours will be determined by the location of the dates in A1:A31 that match the dates in C1 and D1. C1 will determine the

rowvalue and D1 will determine theheightvalue.OFFSET(reference,rows,cols,height,width)

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History