Finding information based upon multiple conditions

[ColdT]

Hi everyone,

I want to populate separate tabs from data in a master table based upon multiple conditions shown in the separate tabs. Each tab is a summary of a particular engineer's shifts over multiple weeks and their results on various projects. Each summary tab has the engineer's name and the number of the week (the two conditions).

Example: On Aaron's tab, I want to show a summary of Aaron's work and his results over a period of several months, as well as who is working the other shift on that day for those projects. So I need to get the following information for Aaron's tab from the master table:

Week 1, Aaron is working the morning shift and Zeb is working the afternoon shift,
Week 2, Bill is working the morning shift and Aaron is working the afternoon shift
Week 3, Aaron is working the morning shift and John is working the afternoon shift
etc etc

I also need to know how much work each engineer pairing has achieved in a particular week on a particular project.

The engineer working in each shift may vary from week to week, as will their results. There is no formula for when a particular engineer will be working either morning or afternoon or whom they will be working with.

I've posted a sample to give a clearer idea of the information I have and what I'm attempting to achieve.

I have dozens of engineers and the weeks will extend for some time (over approx. a three year period).

Thanks in advance,
CT

[DonkeyOte]

There will be a whole variety of ways to do this, based on your upload here is one approach:

Engineer sheets

B6:
=INDEX('Master Table'!B$3:B$100,MATCH($A6,'Master Table'!$A$3:$A$100,0))
copied down as required

C6:
=LOOKUP(REPT("z",255),CHOOSE({1,2},$A$1&" Not Found",LOOKUP(2,1/(('Master Table'!$A$3:$A$100=$A6)*(('Master Table'!$C$3:$C$100=$A$1)+('Master Table'!$D$3:$D$100=$A$1))),'Master Table'!C$3:C$100)))
copied down as required and copied across to Column D

E6:
=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,LOOKUP(2,1/(('Master Table'!$A$3:$A$100=$A6)*(('Master Table'!$C$3:$C$100=$A$1)+('Master Table'!$D$3:$D$100=$A$1))),'Master Table'!E$3:E$100)))
copied down as required and copied across columns F & G

Adjust ranges etc and add additional handlers as required.

[JBeaucaire]

As I'm not a big fan of array formulas, so I added a couple of INDEX-Key column on your data sheet. Just copy the first formula down as far as needed to create the keys, then hide the columns if they are unappealing.

Then simple INDEX/MATCH formulas on the individual sheets will do the rest.

Interestingly, your decision to put the SheetName into cell A1 makes these sheets into "instant templates". Copying a sheet and giving it a new name will immediately reassess the sheet's contents for the new Employee.

[ColdT]

Thank you both for the quick and informative responses. They solved the problem.

Interestingly, your decision to put the SheetName into cell A1 makes these sheets into "instant templates". Copying a sheet and giving it a new name will immediately reassess the sheet's contents for the new Employee.

That was definitely my intent. If I could get your formulas to dynamically update based on the name of the tab, they'd make even better templates.

Thanks again,
CT

[JBeaucaire]

If I could get your formulas to dynamically update based on the name of the tab, they'd make even better templates.

Um, you missed my point, my formulas DO that. I've incorporated cell A1 into the formulas as the central matching piece of data, so it DOES reassess the data for the name of the person listed in cell A1, and since you display that name by the sheetname....it IS dynamically doing as you wish.

If I'm missing some other component, let me know and I'll adjust it.

[ColdT]

Um, you missed my point, my formulas DO that. I've incorporated cell A1 into the formulas as the central matching piece of data, so it DOES reassess the data for the name of the person listed in cell A1, and since you display that name by the sheetname....it IS dynamically doing as you wish.

If I'm missing some other component, let me know and I'll adjust it.

Even though the formula references A1, the name of the sheet (bob in the below example) is static and has to be updated for each sheet:

Please Login or Register  to view this content.

In the above example, bob's name is static. I wasn't able to use my little formula snippet to automagically draw the current tab's name into the sheet refererence within your formula (as I do in cell A1 of each worksheet).

One other thing that I forgot to include in the sample data is that there are weeks when two of the engineers will not work (always the same week) e.g. Bob and Bill may not work in week 8. The formula needs to account for that and show "Not Working" or something to that effect.

To do the above, I added in:

Please Login or Register  to view this content.

It's a temp fix at best because it has to be different than the rest of the formula.

Thanks again,
CT