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
0
8/5/2008 2:57:50 AM
excel 39879 articles. 2 followers. Follow

3 Replies
640 Views

Similar Articles

[PageSpeed] 1

> 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" <retailmessiah@gmail.com> wrote in message 
news:6afc7634-708f-458a-ac45-f73d22d26a36@59g2000hsb.googlegroups.com...
> 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 


0
demechanik (4694)
8/5/2008 1:31:55 PM
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(F2>INDIRECT("F2:F"&ChiCount+SeaCount+1),1,0)))
F2: 
=IF(ROW()-1>ChiCount,IF(ROW()-1-ChiCount>SeaCount,"",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
> 
0
Utf
8/5/2008 1:38:00 PM
On Aug 5, 12:57 pm, retailmessiah <retailmess...@gmail.com> 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

0
KenCJohnson (314)
8/5/2008 3:20:08 PM
Reply:

Similar Artilces:

Validating Data on a Form with a subForm
I have a form with subforms with data validation in the main Form's Before_Update Event. The problem that I am encountering is when the user has not updated all required data but enters the subform the Form Before_Update Event fires and the validation will fail. Is there a way to know what the next object has been selected might be or how I would not perform the Form's validation when the subform has been selected? Any help will be appreciated!!! No. Access saves the main form's record at the point when you move from the main form into the subform. Any record that fails v...

Get Computer Listing
I don't know if this is the correct forum, but I'm creating a remote installation program using VB.NET 2005 and am looking for a really fast way to scan the computers in my domain and create a listing them in a treeview. Is there a way to grab the computer information in the domain and display it quickly from a central workstation programmatically in VB.net? Thanks On Feb 1, 4:29=A0pm, Sean Winfield <swinfi...@cinci.rr.com> wrote: > I don't know if this is the correct forum, but I'm creating a remote > installation program using VB.NET 2005 and am looki...

when selecting contacts from drop down the list is blank
When selecting contacts from the drop down (ie if I am composing and click on the "To:" field) my contact list is blank. If I type a letter in the "Type Name or slect from list" field, all my contacts come up. What needs to be done so all my contacts are visible when I select contacts from the drop down ? Thank you mikebach <it_mikeb@hotmail.com> wrote: > When selecting contacts from the drop down (ie if I am composing and > click on the "To:" field) my contact list is blank. If I type a > letter in the "Type Name or slect from list" f...

Biztalk or Scribe for our data integration?
We recently started using CRM 3.0 and the time has come to decide on a data migration plan. So far I've looked at Scribe and it seems promising but my higher-ups are suggesting we take a look at BizTalk. Eventually we will need to pull (two way integration will be required with at least two legacy systems) data from several legacy systems including flat files, SQL Server and a Progress database to name a few. The possibility also exists that we'll want to accept Word, Excel and HTML documents from customers and have information they contain added to our database. Is BizTalk cap...

Data value display attributes linked to table attributes
Is there a way to cause data value points to take on attributes such as bold, italic or color set at the table from which they are derived? For example, suppose I was charting hits at several web sites as function of time, and the some characteristic of the site was being altered back and forth (say font "A" and font "B") as part of an experiment. I can easily produce a line graph x=hits y=time for the various sites, but these is no way to visualize which condition (A or B) was in effect at each data value point. I'd like to be able to display the portion of each li...

Public Folders are blank in Global Address List...
I have a rights problem on publc folders. Domain Admins are able to see public folders in Global Address List but when a Domain user look in the Global Address List all the public folders are blank. I don't know where to look... The OS is Windows 2003 and Exchange 2003 SP1 Thanks! Morten ...

Money 2005 Standard payee dropdown list
My list of payees (approx. 30-40) is visible in the payee list but none are there when clicking the dropdown box in the transaction form. Adding a new payee in the dropdown box adds it to the general payee list though. Typing the first letter of a payee will allow the "hidden" payee to appear in the payee field but I wish to see the entire payee list in the dropdown box. Searched settings and setup info without finding solution. The payee list normally shows those payees used in the last year... could any of the ones you don'#t see be ones older that that? It would be...

Sorting #5
Hi All, Currently my Excel 2003 sorts in the following order: 1 2 3 4 1A 1B 2A 2B A B I want to be able to sort in this order: 1 1A 1B 2 2A 2B 3 4 A B I have looked in excel and was unable to find a way to do this. Any suggestion? enter 1,2,3,4 as '1,'2,'3 '4 now try sort ================= On Fri, 22 Oct 2004 08:34:08 -0400, VT <vtisix@comcast.net> wrote: > Hi All, > > Currently my Excel 2003 sorts in the following order: > > 1 > 2 > 3 > 4 > 1A > 1B > 2A > 2B > A > B > > I want to be able to sort in this order...

Need to extrcat data from one Excel file to an other
Hi there I'm looking form a solution to press on button in a Excel sheet which ill exctract some datas from an other Excel sheet which can be close or opened. It should beworing on Windows XP, 2003, Vista and 7 as well with 32 bits and 64 bits. Thanks in avance for your advise and best regards Tra Browse through the example codes found at Ron de Bruin's site. http://www.rondebruin.nl/tips.htm Look at the 'Copy/paste/merge" section. Should be some good material to start with. Gord Dibben MS Excel MVP On Sat, 28 May 2011 19:53:03 +0200, Tra Sumaka <trasumak...

enter data to different tables through one form
i have 5 tables. all of them has pers_no as primary key. they have one to one relation ship through the primary key amongest them. i want to create one form having a text box pers_no where i will enter the data and i want the data to get entered in each table. so how i am not able to do that. i request all you guys to help me. thank you. Sorry for the bad news, but that's not the right way to use Access. You cannot write to 5 tables from one form like that. If you are subclassing, you could create a form for entering the main form info, and then 5 subforms (probably on tab pages) f...

Scatter plot
I have an XY scatter graph with some points in it. My data in the spreadsheet looks like this: [Title] [X-value] [Y-value] [size] Data 1 | 10 | 10 | 1 Data 2 | 33 | 44 | 5 What I want to do is to change the size of the data point according to the value I specify. Right now, I have to individually modify each point and add a number corresponding to the size. Is there anyway to take the size-value from the spreadsheet and change the size of the point in the graph? In the long run, I am also looking to modify the color. If the solution requires some VBA coding, I am up for that. Any sugges...

distribution list #22
I dont want to show the users names on the to: line when sent. How can I turn this off and just send the name of the distribution list, not the individuals. Wally b <anonymous@discussions.microsoft.com> wrote: > I dont want to show the users names on the to: line when > sent. How can I turn this off and just send the name of > the distribution list, not the individuals. You can't with a DL. Outlook expands the DL propr to sending. Instead, use Wordmail Merge. -- Brian Tillman ...

Importing data #2
hi I would like to import data from the web and although this is quite simple in Excel 2003 the new data (that comes in upon refresh) replaces the old. I was wondering if there is a way to keep previous history of the data, make Excel fill in adjacent cells with the new data for example. Thanks for taking the time to read this George hi, if you are using Microsoft query, the answer would be no. The Micorsoft query becomes a named range in excel that is linked to the query. by desing it expands and contracts at refresh to accommidate new data. I think to accomplish what you want yo...

Offsetting Chart data
I require to view data in chart format, the problem is as the data lines are so close I need to offset the data to view all on one chart for comparison. Can anyone help me with this?? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ What does that mean? "Offset the data?" If you want to see only a subset of the data, see the Dynamic Charts page of my site. You should be able to adapt one of the examples to your needs. -- Regards, Tushar Mehta, MS MVP -...

dynamic charts using named formulas
I have dates in the first row I have values in the second row I use tecnique of named formuls of offset function (tushar Methta) so that the chart is updated when data for new dates are added. now after some time I want to delete the older data If I delete the columns of older dates the named formula get messed up and I get error message that three is some wrong reference. actulally in the offset formula the starting cell is relaced by "REF" Then I have to redo the whole exercise any solutions. If the first row is some sort of header that doesn't get deleted, use it as the ba...

Not sorting as expected
I have a database some 10 columns wide and 1200 rows deep. When I sort by one particular column it sorts BLANKS before text meaning that the data appears at the bottom of the database although still in alphabetical order. I have the formatting set to GENERAL for the whole column and do want the blank cells. None of the other columns do this. I am totally perplexed as I thought Excel sorted blanks nearer the end of it's priority. I have tried formatting the column to TEXT and even NUMBER also but it still makes the data appear at the bottom of the table. This doesn't make any se...

OLE Object Data Type Question
Hi, a friend of mine supports a very simple Access 2000 database for keeping tracks of his .JPG file. The database has a table with several field, and one of them is of OLE Object data typy. He opens that table, rightclicks OLE Object field, selects Insert Oject and creates a link to a .JPG file. After finishing insertion the OLE Object field contains kind of "MSPicture3" or "MSImage3" (I don't remember exactly the text). Before a month ago he moved his database to another computer and now after insertion into the OLE Object field a link, it contains "...

Data Labels on Clustered Column w/ 3D Visual effect #2
Clustered Columns can have Alignment as "Inside End" This doesn't seem possible with 3D Visual effect Is there a way to resolve this? Yes, use 2D charts. They provide a better representation of the values being plotted without the implied false third dimension. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ emwashburn wrote: > Clustered Columns can have Alignment as "Inside End" > > This doesn't seem possible with 3D Visual effect > > Is there a way to ...

Separating a comma separated list
Not having a great deal of experience with macros, I thought I might see if I could pick the brains of the group. I have to essentially break out a comma separated text string in a single cell into a vertical list, with a reference number thrown in for good measure. Example. Data I have: A B 1 NUMBER RESOURCE 2 101 Smith John,Jones Adam,Brown Philip,Greene Thomas 3 102 Smith John,Jackson Arthur Needs to look like: A B 1 NUMBER RESOURCE 2 101 Smith John 3 101 Jones Adam 4 101 Brown Philip 5 101 Gr...

Trying to mail merge to email
I set up a mail merge but when I tried to finish the merge to an e-mail that icon was grayed out. Does anyone know why this would be? You need to have Entourage set as your default email program. HTH |:>) Bob Jones [MVP] Office:Mac On 1/12/10 1:39 PM, in article 5F41ACE6-2801-46E7-ABCD-2C5D0EE1EC86@microsoft.com, "Leslie" <Leslie@discussions.microsoft.com> wrote: > I set up a mail merge but when I tried to finish the merge to an e-mail that > icon was grayed out. Does anyone know why this would be? Entourage is not your default email progr...

XML Insert data C# ASP.net 2.0 Need to know how to insert by speci
Hello, I have the following XML Schema: <?xml version="1.0" encoding="utf-8"?> <Schedule> <Day Name="Monday"> </Day> <Day Name="Tuesday"> </Day> <Day Name="Wednesday"> </Day> <Day Name="Thursday"> </Day> <Day Name="Friday"> </Day> <Day Name="Saturday"> </Day> <Day Name="Sunday"> </Day> </Schedule> I want users to be able to input the following tiomeblocks for a scheduling s...

Getting data from Access
I am trying to use the Get External Data function to pull data from an Access query however I keep getting an error that says: "Microsoft Query: msqry32.exe - Application error The instruction at 0x000000072 referenced memory at 0x000000072. Memory could not be read Click OK to Terminate Click on CANCEL to debug" I am using Excel and Access 2000. Does anyone have any idea why I keep getting this error? Check out this page http://www.rondebruin.nl/accessexcel.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "NChris" <NChris@discussions.micr...

Cannot get datagrid to display data
Hi, I have an XML file that I was hoping to use as a database Here is the code that I have copied that should display the data namespace File_Generation_System { public partial class vrholds : Form { public vrholds() { InitializeComponent(); XmlDataDocument xmlDatadoc = new XmlDataDocument(); xmlDatadoc.DataSet.ReadXml("vrdatabase.xml"); DataSet ds = new DataSet("vrdata DataSet"); ds = xmlDatadoc.DataSet; dataGridView1.DataSource = ds.DefaultViewManager; } But what I g...

SQL for list manipulation (a mess)
Hello, I have a set of queries which have taken on a life of their own! I don't believe there's room here for all the SQLs (nor would I wish that on anyone) but there has to be a simplier way to do this. I have a form (zfmMain) which has a listbox (lstEMainE), a toggle (tglECInactive) and subform (subfmMainE) with a listbox (lstECsubEc) with 8 or 9 columns dependeng on the selection in lstEMainE (specific Engagement or "*" - All), three toggles (tglECOpen, tglECHold, tglECClosed) and 8/9 label column headings (lblEC1…lblEC9). In VBA on the Click Event fo...

Alphabetical sorting
In Windows mail when I go, Create mail, click on "to" the select recipients are not in alphabetical order. How can I sort this please? Yet if I select contacts in the toolbar they are sorted. In the Contacts folder: Change the View to "Details". Right-click on the "Name" column heading, click on "First Name", then click on "Last Name" Click on the "Last Name" heading to sort by that, or click on "First Name" heading to sort by that. In a Create Mail window: Click on To: Click on the "Name" column he...