dropdown returns results (comma separated data from another tab) or should i use access?

scheduleHarribone.xlsx

I have created a new Sheet in the file. Your Search tab remains but doesn't do anything.

To make things easier I've made B2 and B3 on Sheet1 drop down lists instead of adding drop downs as you have.
To do this select the cell then choose Data>Data Validation. Set 'Allow' to list and then 'Source' to the range you want to use.

C2 returns the day code (m,t,w etc). C3 returns a Column number for that time (this is from the row added under the times in the Data tab).

Col F list all the users. Col E looks for that user on the data tab and check the cell for the given time. if it finds the day code then it is assigned the next lowest number.

The 'Names Found' table then shows these names. The reason for this bit is because I have made it so that you can hide the orange areas (Col C E and F). Once hidden this looks a lot cleaner.


Hope this helps. Let me know if I've missed anything.

The number represents the column number so that the VLOOKUP formula knows which column the time is in.

To further improve this for looks you can hide Col I on sheet1.
Then select J3. Choose Home>Conditional Formatting>New Rule.
Select 'Use a formula to determine...'.
Type in =J3=""
Click 'Format' and set the fill to White and in Border remove the Left/Right/Bottom border (The top border should be left untouched as a grey dotted line).
Click OK. Click OK again.

With J3 still selected click Home>Format Painter.
Select cells J4:J14 in one go.

Change the formula in J3 to =IFERROR(VLOOKUP(I3,$E$3:$F$14,2,0),"None Found")

Now the 'Names Found' will hide any blanks and also show 'None Found' when there are no results (good for other users so they know that nothing is broken!).


Once you are happy that the solution given does the job can you please go to Thread Tools at the top of the page and mark as SOLVED. That way other users doing a search will know to look here if they have a similar need.