I think this may be too easy but.......

[sdurber]

its driving me crazy, I just can't figure it out.

I have a list of months (Jan - Dec) in Column A and a list of hours worked (per month) in Column B.

I want to set up a mechanism that will allow me to select two dates from the list and then be given the total hours worked during this period.

Is there a function or or combination of functions that I can use to acomplish this?

So the solution will go something like this
Enter the two dates
Find the position of date 1
Find the position of date 2
Go to the next coloumn annd Sum everything in between the two references.

I should be able to do this but I cant, after 2 days Im very close to giving up, unless you guys can help.

 

[Qroozn]

This message was edited by Qroozn on 2002-05-01 23:03

 

[RichardS]

There are probably tidier ways of doing this.
In Sheet1, I created a list of months from Jan to Dec in Cells A1 to A12. I put the time values in cells B1 to B12.

In Sheet2, cells A1 and A2, i entered the months to add between (inclusive). In cell B1, I entered the formula
=MATCH(A1,Sheet1!$A$1:$A$12,0)
and copied down to B2. This finds the location of the months.

In cell C1, I entered the formula
="Sheet1!B"&B1
and copied down to cell B1. This created the references i wanted added.

In the cell I wanted the result to appear, I entered the formula
=SUM(INDIRECT(C1):INDIRECT(C2))
and blow me down, it worked.

As I said, there are probably more elegant solutions, or you may be able to wrap the whole lot up into one formula, but it's the best I can do at the moment.

Richard

 

[RichardS]

As I said in the earlier post, you could wrap it all up in one formula;

=SUM(INDIRECT("Sheet1!B"&MATCH(A1,Sheet1!$A$1:$A$12,0)):INDIRECT("Sheet1!B"&MATCH(A2,Sheet1!$A$1:$A$12,0)))

Change the relevant sheet names and references to suit.

HTH

Richard

 

[sdurber]

Wow this is brilliant thanks very much.
I was still in the process of applying your sugestions from the earlier post when I read your final instuctions.
Im off to try it.

Cheers mate

Steve

 

[sdurber]

It worked perfectly.

Thanks for all your suggestions and help guys