Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Funtions for pulling data
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 a 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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Funtions for pulling data
=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. -- Regards, Tom Ogilvy "Quix " wrote in message ... 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 job related data. Since each client will be listed several times, for past, current, and future jobs... I want to be able to return only the data I need from 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" in one cell indicating what week i'm working on. After that, I'd like the "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 the condition, but i can't get my conditions to work. The problems i'm having a 1. The Week # and Client Names occur multiple times. Vlookup won't work 2. I can't isolate the row where the two conditions match. If anyone could give me guidance, It would be greatly appreciated. Thanks --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling data from other wb based on data to populate dropdown list | Excel Discussion (Misc queries) | |||
Tying to extract all data from a pivot pulling from external data | Excel Discussion (Misc queries) | |||
Linking two spreadsheet, pulling data from one cell to another, data is being truncated | Excel Worksheet Functions | |||
database query not showing foxpro database How I import data | New Users to Excel | |||
Pulling data out of a large database | Excel Discussion (Misc queries) |