Complicated LOOKUP Question

[Req]

Hi Guys,

I'm having trouble with a difficult issue on Excel 2000.

Here is roughly what my current cell looks like.

A1
=SUMPRODUCT(('Sheet 1'!$B$297:$B$303=B118) * ('Sheet
1'!$Q$297:$AD$303))

This is producing a result where I can see how many hours an employee
has worked on a particular project.

What I need is for A1 to reference to a cell which has dates in a drop
down box.

Say my "Start Date" cell is D10 and my "End Date" cell is D11.

I would like to change the values in D10 and D11 and have A1 change
it's data according to the dates placed in the said cells.

Is this possible?

I have attached a crude version of my spreadsheet.

 

[Tom Ogilvy]

=SUMPRODUCT(('Sheet 1'!$B$297:$B$303=B118)*('Sheet 1'!$C$297:$C$303=>D10)
*('Sheet 1'!$C$297:$C$303<=D11)* ('Sheet 1'!$Q$297:$AD$303))

if your column of dates is column C, Column B is your column of project
identifiers

D10 start date
D11 end date
B111 project ID

columns Q to AD represent a 14 day period, you would need to provide more
specific information.

 

[Req]

Nah.. didn't really help out.

I understand what you mean by throwing in the >= and <= though.

Back the database previously attached.

In psuedo code this is what I want...

* Read the dates cell and locate the dates on Sheet 1
* On sheet 1, next to the name "Employee 1" locate the dates specified
for that employee
* Add the values of the cells which the dates have surrounded.
* Breakdown the values per-project. For example if Sheet 1 reads that
employee 1 did 7.5 hours on project 1 between the dates specified, then
I would like that to display on the results sheet.

However, I need it to read each project. The employee has done 3.3
hours on project 1 and 2.5 hours on project two... therefore under the
heading of project 2 it should enter in the value 2.5

Hrmm... I think my problem is a little conviluded.