maintaining a list on a new tab based on multiple criteria

[jangell]

For my little league program I have multiple tabs set up in an excel sheet

I would like to maintain one master tab and have that master tab populate all of the other tabs based on a set of criteria (but only if ALL criteria are met)

So on the master tab i would like to have a list of names, age groups and whether or not they have confirmed the invite.

If they are of a certain age, are rostered on a certain team and have accepted the invite I would like to return a list of those kids on a specific tab (tab correlates to the age group)

Example - if column A on the Master sheet has a 7 or an 8 AND Column B has a D in it AND E has a y in it

The 8 tab would have a of all kids that met the above criteria.

Any help would be great.

I have a simple example but did not see where I could post a SS

 

[Paradigm_Shift]

jangell,

If I understand you correctly, sounds to me like you could combine the IF function with the VLOOKUP function. This is gonna be difficult to explain so to make it easier, set up this test workbook so that my code will make more sense. This is what I had typed in Sheet1, cells A1:E6:

ID Name Age Confirm? Letter
1 John Doe 8 y D
2 Jay Leno 7 n A
3 Mr Excel 9 y F
4 Stack Overflow 6 n B
5 Tony Man 7 y D

I assigned them an ID so that can be referenced specifically using the VLOOKUP function. Also, you can split the name up, but you might have to alter the vlookup parameters a bit...let me know if you need help with that.

The next step is to set up your "7-8yr old" sheet with the ID numbers in column A, A1 starting w/ 1 and so on. Then basically, if you wanted to populate Column B in the "7-8yr old" tab with all the kids' names that met the criteria you listed, here's what you would enter in the B Cells:

[FONT=georgia,serif]=IF(Sheet1!C2=7,IF(Sheet1!D2="<wbr>y",IF(Sheet1!E2="D",VLOOKUP($<wbr>A1,Sheet1!$A$2:$E$6,2,FALSE),"<wbr>"),""),IF(Sheet1!C2=8,IF(<wbr>Sheet1!D2="y",IF(Sheet1!E2="D"<wbr>,VLOOKUP($A1,Sheet1!$A$2:$E$6,<wbr>2,FALSE),""),""),""))[/FONT]

I know it's long and probably confusing but if you break it up, it makes sense. Now, this will basically leave any cells in Column B that DON'T meet ALL criteria blank, so you might have to delete some rows when it's all said and done. But I don't know of a better way w/out using VBA to do this...maybe somebody else knows of a better way, but this works.

If you want to do the same thing for, say 11-12 yr olds, then change the values in the formula where it says "C2=7" and "C2=8" to 11 & 12 respectively. I'm not sure what you meant by the "y" and "D," but I just went with it. So hopefully you can modify this to fit your needs a bit better.

Hit me back w/ any further questions/updates. Best of luck to you!