I have a table of events (1 row per event) containing a list of participants (1 participant per column).
Now on another sheet I would like to create a drop down (Data Validity Range) populated only with the participants of a selected event.
Example (Event Table)
1: Event A; 12.12.2017; Berlin; Michael; Freddy; Robby
2: Event B; 11.11.2017; Paris; Phil; George; Bono; David
Selector: Event B
Dropdown containing names only from Row 2 (Event B)
By using INDEX I was able to get one value (i.e. D2=Phil). But I can’t figure out how to make INDEX return the whole range D2:G2
How can I get a range with INDEX? Or what is the “correct” way to accomplish this selective dropdown?