First off, let me say thanks to the Ozgrid community that has helped me over and over through these forums. You are phenomenal.
Question 1: I need to create a BOTH box that will dynamically pull names from both the "Chicago Office" & "Seattle Office" columns in my sheet. It is extremely preferable to do this without VB Script if possible to avoid the security warnings on opening the sheet. (Our IT department will not budge on this..)
I have two lists of employees. Column A lists the Chicago Office employees. Column B lists the Seattle Office employees. I've been able to successfully define named ranges to work with these as dynamic lists. I can append names, or delete names, and the Chicago, or Seattle boxes (drop downs created with data validation formulas inside named ranges) will reflect the updated names correctly. I have been unable to make a single drop down with all the names from both offices, that is updated dynamically. I've attached a sheet so that this is easier to understand. Basically I need the "BOTH" drop down to actually work.
The Chicago range is defined as:
The Seattle range is defined as:
In the boxes on the right of the lists, I just have a Data Validation List formula as:
=Chicago and the other as =Seattle
Notes: I've noticed that if there are spaces in the list then it does not display correctly. I've wondered if there is different way to write the formula to make it take into account spaces in the middle of the list, to make it more user friendly.
Thanks for any ideas you have, or any help you can provide. If the attached sheet doesn't work for you, please access it here.