Automatically Generated list based on criteria

**genichigo** · 03-21-2013, 01:08 PM

Hey guys I'm not sure if this makes sense, but im struggling of finding the solution, I can fix it to a degree using index match but i need to assign people numbers.

Please find attached sheet on "sheet 2" is some data I want to have populated using 2 out of 3 data fields.

Date- Must be entered as this will determine the information displayed and will be a variable date (every day data is added)
Name or Team Leader, 1 of each much be Entered, either the team or the individual being reviewed.

What im trying to do, is once the data is entered and calculation ran, from cell B8 and h8 it will automatically list all of the instances that the person or team leader is entered.

for this example if i entered 20130320, for Kirk, James it would display all of the data (excluding the team leader column on each sheet) but will display Name, Code, From and Too into both boxes for evaluation to compare actuals against schedules.

I can't figure an easy or quick way to compile this any idea's?

(P.s. If selected for team leader, it would display several peoples data in order it is represented in each sheet)

**ChemistB** · 03-21-2013, 01:51 PM

First I set up key columns (Col H) on Actual and Schedules Sheets using this formula (example from Actuals!H2)

=IF(A2=Sheet2!$C$3, IF(Sheet2!$J$3="", IF(Sheet2!$F$3=C2,H1+1,H1), IF(Sheet2!$J$3=D2, H1+1, H1)),H1)

This counts off all the rows that match your criteria from Sheet2

Then on Sheet2 in B8 for example, I use INDEX/MATCH to pull back the values in each row where the values increment in Col H

=IF(MAX(Actuals!$H:$H)>=ROW(A1),INDEX(Actuals!$C$2:$C$13,MATCH(ROW(A1),Actuals!$H$2:$H$13,0)),"")
See attachment
Questions?

Important Edit. Need to change that initial
MAX(Actuals!$H:$H)>ROW(A1) TO MAX(Actuals!$H:$H)>=ROW(A1)
for all columns.
Otherwise, you'll not include your last value.

**ChemistB** · 03-21-2013, 02:21 PM

PS. I suggest you use Data Validation and drop down lists to create people names and leader names. One extra space or misspelled name and you have no matches.