Goal
Show in one cell the surnames of band members (all in different cells on a roster page) as members of a particular band (which maintains the same name but changes members based on quarter/monthly), based on both the band name and when a particular show date is.

i.e.
Feb to Jun, Awsum Nuggets members were Johnson, Willis and Tart.
Jul to Dec, Awsum Nuggets members were Harper, Hopper and Jackson.
Awsum Nuggets played a show Feb 2, 2013.
Based on the show date and Band name (as log contains names of other bands that also change qtr/mo, cell should show names according to roster based on when band was staffed by which members.

Log
Show Date | Band | Members (this cell has formula to display member names)

Roster (feeding info)
Arrive | Depart | Band | Mbr1 | Mbr2 | Mbr3 | Mbr4

Current Formula (not working)
=IF(and(($A6:A12=MEDIAN(Roster!A6:B6,)),B6:B12=Ros ter!C6)),Roster!D6:K6,"")
Translation
=if (date in column A is between A6:B6, AND the name in Roster!$C6 is in column B) then display member names from the associated band or ""

Any ideas?

On Thu, 9 May 2013 12:53:36 +0100, Christian Michael
wrote:


Goal
Show in one cell the surnames of band members (all in different cells on
a roster page) as members of a particular band (which maintains the same
name but changes members based on quarter/monthly), based on both the
band name and when a particular show date is.

i.e.
Feb to Jun, Awsum Nuggets members were Johnson, Willis and Tart.
Jul to Dec, Awsum Nuggets members were Harper, Hopper and Jackson.
Awsum Nuggets played a show Feb 2, 2013.
Based on the show date and Band name (as log contains names of other
bands that also change qtr/mo, cell should show names according to
roster based on when band was staffed by which members.

Log
Show Date | Band | Members (this cell has formula to display member
names)

Roster (feeding info)
Arrive | Depart | Band | Mbr1 | Mbr2 | Mbr3 | Mbr4

Current Formula (not working)
=IF(and(($A6:A12=MEDIAN(Roster!A6:B6,)),B6:B12=Ro ster!C6)),Roster!D6:K6,"")
Translation
=if (date in column A is between A6:B6, AND the name in Roster!$C6 is in
column B) then display member names from the associated band or ""

Any ideas?


This is a Rosterfarian post.