Forum Discussion
Help Needed: Nest XMATCH in h-linked drop-down to land on first blank record in the destination Tab
Please stay on the cell with drop-down list, when on ribbon Data->Data validation. It looks like
In Source it could be text as here, or usually reference on the range within workbook. Expand reference with names of new tabs and correct the Source above. It will be like
Marcus_Booth , you are welcome, glad to help
- There is something additional that I would like to build into this project. Let me know if it's ok to post here, or if I should start a new discussion...
I have a table on which will be entered several hundred employees on a tab called 'Staff_List' which also has a column with the name of the office in which they work. When a user navigates to a given 'OfficeName Tab' from the directory that you have so graciously assisted with, and they go to pick a staff name from a drop down, I only want the names of the employees from that office to populate the drop-down under the 'Staff Name' column (based on the sheet name (specific office), instead of having them see the whole list. There are other columns of data that I want to bring in as well from that same 'Staff_List' table. Can this be done using a formula?As variant:
In Staff_List you manually add names of offices and to the right of each formula as
=TRANSPOSE(FILTER(Staff[Name], Staff[Office]=$E4))That supporting information
could be at any place of the sheet, or even in another sheet which you could hide. It's important it shall be nothing to the right having space to spill names for each office.
Next in each of office tabs put it's name in some cell
and for data validation use formula
=XLOOKUP($B$1$A$5,Staff_List!$E$4:$E$9, Staff_List!$F$4:$F$9)#It'll be the same for data validation on each tab, if only tab name is in the same cell in each sheet. Don't forget # at the end of XLOOKUP formula.
You may check in attached file.