I have a Google Sheet that acts as a weekly calendar for job duties. (first screenshot) In each of the columns are drop-downs to select a name.
I have a separate sheet that I'm building out to be a dashboard. (second screenshot)
In my example, Michael Scott is in Bay 1A Monday and Tuesday, but in Bay 2 on Thursday and Friday. What I need is to be able to return the appropriate bay number to the dashboard wherever Michael Scott's name shows up.
Hoping that explanation makes sense.
EDIT
I forgot to mention that the drop-downs are a merge of columns O and P.
EDIT 2 Current formula being used.
=IF(IFERROR(CELL("contents",OFFSET(INDEX(FASHION!$J$10:$J$195,MATCH($C4,FASHION!$J$10:$J$195,0)),1,4)),"")="",IFERROR(CELL("contents",OFFSET(INDEX(FASHION!$J$10:$J$195,MATCH($C4,FASHION!$J$10:$J$195,0)),-6,4)),""),IFERROR(CELL("contents",OFFSET(INDEX(FASHION!$J$10:$J$195,MATCH($C4,FASHION!$J$10:$J$195,0)),-6,4)),""))
Where cell C4 is the reference to the person's name.
CodePudding user response：
try:
=ARRAYFORMULA(SUBSTITUTE(REGEXREPLACE(TRIM(QUERY(IF(
FILTER(A8:J48, MOD(COLUMN(A8:J48), 2)=0)=L3,
FILTER(A2:J42, MOD(COLUMN(A2:J42)-1, 2)=0), ),,9^9)),
"(?i)bay ", ), " ", ", "))
