On Aug 5, 12:57 pm, retailmessiah wrote:
First off, let me say thanks to the Excel Usenet community that has
helped me over and over through these newsgroups. 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..)

Detail:
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:
=OFFSET(Sheet1!$A$2:$A$11,0,0,COUNTA(Sheet1!$A$2:$ A$11),1)
The Seattle range is defined as:
=OFFSET(Sheet1!$B$2:$B$11,0,0,COUNTA(Sheet1!$B$2:$ B$11),1)

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.

Question 2: Once the first piece is complete, I'll be in good shape.
Really, Question 1 is the big one. I did however want this merged list
to also be sorted alphabetically. I thought, oh, I'll just wrap a
SORT() function around the working formula, and bang I'll have it
alphabetized. Then I realized that Excel does not have a SORT()
function. How might I sort this merged list, so that it appears
alphabetically in the drop down list?

Thanks for any ideas you have, or any help you can provide. Since I
can't attach files on here, please click here for the sample sheet:http://www.pixeldev.net/john/DynamicLists-Example.xls

Thanks,
John

For Q1 try this in C1...

=IF(A1<"",A1,IF(ROWS($1:1)<=(COUNTA(Chicago)
+COUNTA(Seattle)),OFFSET($B$1,ROWS($1:1)-
MATCH(LOOKUP("zzz",Chicago),Chicago,0)-1,0),""))

To define the named range for "Both" use...

=OFFSET(Sheet1!$C$1:$C$22,0,0,SUMPRODUCT(--(Sheet1!$C$1:$C$22<"")),1)

Ken Johnson