Names based on team names

**inayat** · 10-10-2011, 03:58 AM

Hi All,

I have a master sheet that contains all the data. Then I have individual team sheets. What I need is to take the data from the master sheet and put it in the team sheets based on the team of the individual. I tried a formula that I had saved from a previous post in this forum and tweaked it a little. It is getting all the names. However, it is giving a 0 when it does not find any name. Attached sheet may help. Please suggest how to get the 0 removed. Also, if there is any other way to do this.

Cheers-
Inayat

**TMS** · 10-10-2011, 04:27 AM

Try:

=IF(ISERROR(INDEX('Overall Details'!$A$2:$A$51,SMALL(IF('Overall Details'!$B$2:$B$51='Team 1'!$B$2:$E$2,ROW('Overall Details'!$A$2:$A$51)-1,""),ROWS('Overall Details'!$A$2:A2)))),"",INDEX('Overall Details'!$A$2:$A$51,SMALL(IF('Overall Details'!$B$2:$B$51='Team 1'!$B$2:$E$2,ROW('Overall Details'!$A$2:$A$51)-1,""),ROWS('Overall Details'!$A$2:A2))))&""

That's just added &"" to the end. Confirmed with Ctrl-Shitf-Enter.

Regards

**inayat** · 10-10-2011, 06:55 AM

Thanks that did it...
However I have some additions to this..

- In Column C we have two values "IN" or "Out". If the value is "IN", name will be displayed
- In Column C, if the value is "Out", then in Column D we have a date associated with it. If the date is greater than today(). then only the name should be displayed, otherwise not.

Attached is the sheet.

Mods - Do you think I should open a new thread for this as the question has changed completely, I guess.

**TMS** · 10-10-2011, 07:09 AM

Do you think I should open a new thread

The original question has been answered and you'll get more interest with a new thread.

Thanks for the rep. You should mark this thread as solved and start a new one.

Regards