Merge & Sort Dynamic Lists w/ Data Validation

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

Question 1 is the big one

Here's one play to deliver the "big" one:
http://freefilehosting.net/download/3kgmc
AutoCombine Dynamic Lists.xls

In C2:
=IF(ROWS($1:1)COUNTA(A:A)-1+COUNTA(B:B)-1,"",IF(ROWS($1:1)COUNTA(A:A)-1,INDEX(B:B,ROWS($1:1)+1-(COUNTA(A:A)-1)),INDEX(A:A,ROWS($1:1)+1)))
Copy C2 down to say, C30, to cover the max expected extent ie the max number
of combined names. This auto-merges the names from both lists, those from
Chicago followed by those from Seattle

Then create the dynamic range: BOTH
to refer to:
=OFFSET(Sheet1!$C$2,,,SUMPRODUCT(--(Sheet1!$C$2:$C$30"")))

Then you can use BOTH in the DV to get the required results
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik
---
"retailmessiah" wrote in message
...
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

You asked for it, you got it. I will cover from start to finish.
A1: Chicago
B1: Seattle
A2:A? Chicago names
B2:B? Seattle names
F1: Both
E2**: =IF(F2="","",1+SUM(IF(F2INDIRECT("F2:F"&ChiCount+ SeaCount+1),1,0)))
F2:
=IF(ROW()-1ChiCount,IF(ROW()-1-ChiCountSeaCount,"",INDIRECT("B"&ROW()-ChiCount)),INDIRECT("A"&ROW()))


E2 is an array formula. When entering, be sure to press CTRL+SHIFT+Enter to
commit the formula.
Copy E2 and F2 down as needed.
G1: =IF(ROW()ChiCount+SeaCount,"",VLOOKUP(ROW(),$E:$F ,2,FALSE))
Copy this formula down as needed.

Defined variables
ChiCount =COUNTA($A$2:$A$100)
SeaCount =COUNTA($B$2:$B$100)

Defined Lists
Chicago: =OFFSET(Sheet3!$A$2,0,0,ChiCount,1)
Seattle: =OFFSET(Sheet3!$B$2,0,0,SeaCount,1)
Both: =OFFSET(Sheet3!$G$1,0,0,ChiCount+SeaCount,1)

The problem is that Data Validation lists are single row or single column
only, and therefore, a side by side as you have will not work. So a separate
list had to be created, this I did in column F. However, the column F is
unsorted, so I had to determine who's name is first, then second, etc. This
was done in column E (so that I can do a vlookup). After figuring out the
order, I then had to actually put the list in order, this was done in column
G, which is where the final, sorted list is.

I will say, however, if you only have first names, and there are duplicates,
you will run into issues. But this should give you a start.

Hope this helps.
--
John C


"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

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