Database Funtions for pulling data

[Quix]

Hello,

I'm having a problem with filtering out needed data from a workbook.

here's the senario...
I have a Status Report that I need filled out for about 20 or so jobs.
Each job is assigned a "Job Number", "Client Name" and "Job Week".
This Data is organized by colums in a master spreadsheet with other jo
related data.

Since each client will be listed several times, for past, current, an
future jobs... I want to be able to return only the data I need fro
the jobs' assigned for a particular date into another workbook.

Example:
JOB #_____JOB WEEK___CLIENT NAME
111111_____6.1________ClientA
222222_____6.1________ClientB
333333_____6.2________ClientA
444444_____6.2________ClientB
555555_____6.3________ClientA
666666_____6.3________ClientB


What I want to be able to do...
In another book/sheet, i want to be able to be able to enter "6.2" i
one cell indicating what week i'm working on. After that, I'd like th
"Job #" to update for all the exsisting clients. If it was '111111
for Client A, It should automaticaly update to '333333'

Example:
-------------------
Week #: 6.2

CLIENT NAME____JOB #
ClientA________333333
ClientB________444444
--------------------

the next week all I do is change the Week #


--------------------
Week #: 6.3

CLIENT NAME____JOB #
ClientA ________555555
ClientB ________666666
---------------------

I think it might need a DGET funtion with an If/AND fuction in th
condition, but i can't get my conditions to work.

The problems i'm having are:
1. The Week # and Client Names occur multiple times. Vlookup won'
work
2. I can't isolate the row where the two conditions match.

If anyone could give me guidance, It would be greatly appreciated.

Thank

 

[Tom Ogilvy]

=Index(Sheet1!$A$1:$A$50,Large(sumproduct(-(sheet1!$B$1:$B$50=$Week$Number),
-(Sheet1!$C$1:$C$50="Client Name"),Row($A$1:$A$50),1),1)

Where $Week$number would be replaces with the Cell location where you type
in the Week Number
And Client Name would be replaced with the cell location where the client
name is located on that row.

You can then drag fill this formula down the column.