Solved

Excel 2007 Form List or Combo Box based on selection in other box

Posted on 2013-11-05
23
551 Views
Last Modified: 2014-02-03
I want to create a form for techs to use to report action taken in the field.  I want to select a state from a list box and show school districts from that state in the districts list box.  I also want to show only schools in that district in the schools list box.  I need help.  Worksheet attached.
Sample.xlsx
0
Comment
Question by:BrainFrost
  • 13
  • 9
23 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39626274
Your list doesn't specify which School belongs to which District or which District belongs to which state, so I can't provide any examples with your data.

What you are looking to do is create dependent drop down lists.  Contextures offers a nice guide on doing this.  The information can be found here: Create Dependent Drop Down Lists
0
 
LVL 80

Expert Comment

by:byundt
ID: 39626342
Your question may be generically described as "How do I create dependent dropdowns?"

I split your data into two tables: one for a list of states, and the other for the schools. The table for the schools needs to have a column for the state it is in, so I added that. That table also needs to be sorted by the state column.

Having done the above, you can create a pair of dependent dropdowns using an OFFSET formula for the Source of the second dropdown:
=OFFSET(INDEX($D$2:$D$15,MATCH($J$1,$C$2:$C$15,0)),0,0,COUNTIF($C$2:$C$15,$J$1))
SampleQ28286411.xlsx
0
 

Author Comment

by:BrainFrost
ID: 39627246
Thank you for your help.  Your sample file accomplishes my goal with the offset.  I have a question:  When the data is "refreshed" from the Access database and gets more states, districts and schools added into their columns, will this method include the new information or will the ranges in the data validation need to be manually updated?  I hope to design the form once and have it work with updated data without having to change the design.  Can this be done?
0
 
LVL 80

Expert Comment

by:byundt
ID: 39627435
You will need dynamic named ranges if you want the dropdown lists to change automatically as data are added to your tables. You will also need to sort the data in the Schools table by the state.

Named range "States" for first dropdown list:
=Table1[States]

Named range "Schools" used in formula for second dropdown list:
=Table2

Data validation Source for first dropdown list:
=States

Data validation Source for second dropdown list:
=OFFSET(INDEX(Schools,MATCH($J$1,$C$2:$C$15,0),2),0,0,COUNTIF(INDEX(Schools,,1),$J$1))
SampleQ28286411.xlsx
0
 
LVL 80

Expert Comment

by:byundt
ID: 39627453
I missed one reference to a fixed cell range in the Source for the second data validation. It should be:
=OFFSET(INDEX(Schools,MATCH($J$1,INDEX(Schools,,1),0),2),0,0,COUNTIF(INDEX(Schools,,1),$J$1))
SampleQ28286411.xlsx
0
 

Author Comment

by:BrainFrost
ID: 39646366
To make the workbook and data it contains more realistic and to include the ID numbers that relate one table to another, I've uploaded a new version.  The tables are separated by gray blank columns.  Column header names were chosen to be unique to the sheet while remaining somewhat meaningful.  The first table lists STATES, the next lists school DISTRICTS, the next lists SCHOOLS, and the next lists ROOMS in the schools. The state in the STATE table relates to the DistState in the DISTRICT table.  The DistID in the DISTRICT table relate to the DID in the SCHOOLS table.  The SchID in the SCHOOLS table relates to the SID in the ROOMS table.  The data in the school column of the ROOMS table is redundant and unnecessary, but it helps to see the relationship between the SchID and SID columns.  The actual form begins at column X, row 3.

If you can help me get dependent drop down lists that work with dynamic data on this worksheet, I'll be able to use the code without the need to change it.
XLsvcFormTest.xlsx
0
 
LVL 80

Expert Comment

by:byundt
ID: 39646710
I created the following named ranges:
ddDistrict
=OFFSET(INDEX(Districts,MATCH($X$3,INDEX(Districts,,1),0),3),0,0,COUNTIF(INDEX(Districts,,1),$X$3),1)

ddSchool
=OFFSET(INDEX(Schools,MATCH(INDEX(Districts,MATCH($X$5,INDEX(Districts,,3),0),2),INDEX(Schools,,1),0),3),0,0,COUNTIF(INDEX(Schools,,1),INDEX(Districts,MATCH($X$5,INDEX(Districts,,3),0),2)),1)

ddRoom
=OFFSET(INDEX(Labs,MATCH(INDEX(Schools,MATCH($X$7,INDEX(Schools,,3),0),2),INDEX(Labs,,1),0),4),0,0,COUNTIF(INDEX(Labs,,1),INDEX(Schools,MATCH($X$7,INDEX(Schools,,3),0),2)),1)

ddServiceCode
=OFFSET(INDEX(Codes,MATCH(INDEX(CodeCat,MATCH($Z$11,INDEX(CodeCat,,2),0),1),INDEX(Codes,,1),0),2),0,0,COUNTIF(INDEX(Codes,,1),INDEX(CodeCat,MATCH($Z$11,INDEX(CodeCat,,2),0),1)),1)

Using those named ranges, I was able to link your dependent dropdowns.

These formulas are dependent on the column index numbers, and so will get messed up if you move columns within a table.
XLsvcFormTestQ28286411x.xlsx
0
 
LVL 80

Expert Comment

by:byundt
ID: 39646734
I redid the named ranges for the dropdowns using structured references to table columns. This allows you to move your columns within the table without needing to redo the formulas.

ddDistrict
=OFFSET(INDEX(Districts[District],MATCH($X$3,Districts[DistState],0)),0,0,COUNTIF(Districts[DistState],$X$3))

ddSchool
=OFFSET(INDEX(Schools[SchoolName],MATCH(INDEX(Districts[DistID],MATCH($X$5,Districts[District],0)),Schools[DID],0)),0,0,COUNTIF(Schools[DID],INDEX(Districts[DistID],MATCH($X$5,Districts[District],0))))

ddRoom
=OFFSET(INDEX(Labs[Room],MATCH(INDEX(Schools[SchID],MATCH($X$7,Schools[SchoolName],0)),Labs[SID],0)),0,0,COUNTIF(Labs[SID],INDEX(Schools[SchID],MATCH($X$7,Schools[SchoolName],0))))

ddServiceCode
=OFFSET(INDEX(Codes[Description],MATCH(INDEX(CodeCat[CatID],MATCH($Z$11,CodeCat[Category],0)),Codes[Cat],0)),0,0,COUNTIF(Codes[Cat],INDEX(CodeCat[CatID],MATCH($Z$11,CodeCat[Category],0))))
XLsvcFormTestQ28286411x.xlsx
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39646819
The structured reference formulas are all variations on a common theme. The first one (for ddDistrict) is the most basic:

COUNTIF(Districts[DistState],$X$3)       the number of items that will be in the dropdown list

MATCH($X$3,Districts[DistState],0)       index number of first item that matches $X$3

INDEX(Districts[District],MATCH($X$3,Districts[DistState],0))     range reference to District of first item that matches $X$3

=OFFSET(INDEX(Districts[District],MATCH($X$3,Districts[DistState],0)),0,0,COUNTIF(Districts[DistState],$X$3))         starting at District of first item that matches $X$3, return all the items in that District
0
 

Author Closing Comment

by:BrainFrost
ID: 39647821
The words "Thank You" do not begin to express my appreciation for your kind assistance.  You did so much more than help me - you did the whole project for me!  And so quickly!  Even I don't know the countless hours you saved me in struggling to figure this out on my own.  I COULD NOT BE MORE PLEASED and will heartily recommend your site to anyone I know who needs assistance.  All the best to you and yours.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39648513
BrainFrost,
After sleeping on the problem, I realized that the suggested formulas will have a problem if there are duplicates in the columns used for dropdown sources. For example, if "Central High School" appears a half-dozen times in Schools[SchoolName] column--the dropdown for the room numbers will always be looking at the rooms for the first mention of "Central High School" in Schools[SchoolName]--even if that is for a different district altogether.

The solution for this problem is fortunately very easy. You need to make sure that each item in the dropdown lists has a unique ID (i.e. DistID & SchID). You can then capture that ID with a LOOKUP formula like this one for the DistID:
=LOOKUP(2,1/((Districts[DistState]=$X$3)*(Districts[District]=$X$5)),Districts[DistID])

You can then use a much simpler structured formula for the dropdown source. This one is for School names (ddSchool):
=OFFSET(INDEX(Schools[SchoolName],MATCH($AA$5,Schools[DID],0)),0,0,COUNTIF(Schools[DID],$AA$5))

Everything is set up for you in the sample workbook.

Brad
XLsvcFormTestQ28286411.xlsx
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:BrainFrost
ID: 39661028
Hello Brad,

I understand your point about why the LOOKUP is needed.  I don't understand where it is used in the sample file.  There is a problem with the sample in that any State, District and School chosen shows all the rooms on the sheet for selection in the Room drop down box.  It seems the previous sample works properly without the LOOKUP.  I think you matched the proper District and School ID's in that one without the LOOKUP.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39661695
I posted the wrong version of the sample workbook. My apologies. I've attached the correct one. Note the formula in cells like AA5, AA7 or AB13 for the LOOKUP formulas.
XLsvcFormTestQ28286411x.xlsx
0
 

Author Comment

by:BrainFrost
ID: 39666699
Hi Brad, the new file works well and I see how the LOOKUP works.  Thanks.

I've added an Active X control named "Microsoft Date Time Picker Control 6.0 (SP4)" that works well on my copy of the spreadsheet.  Other users get a red X in the control when they open the spreadsheet.  I think they need an add-in or must do something else after they get it.  One is using Windows XP, the others are Windows 7.  Some have Office 2003, others 2007 and one has 2010.  How can I get this form to work for all of them without a red X in the control?
0
 
LVL 80

Expert Comment

by:byundt
ID: 39668128
The people who have a red X may need to set a reference to the library that contains the Microsoft Date Time Picker Control. On my computer (which has Excel 2013 installed), you do this by checking the box for "Microsoft Windows Common Controls-2 6.0 (SP6) in the VBA Editor Tools...References menu item. If you open the Tools...References menu item and see one or more items marked (Missing), then there's the culprit. Microsoft may have changed the name of the library, and thus broken the reference for those users. But you can restore it on a case by case basis by finding the version of the library that they have installed.

Here is a third party (free) alternative to the Microsoft Date Time Picker control if you are using it to pick a date. It was developed by Microsoft Excel MVP Ron deBruin, and includes alternative versions for Excel 2003 through 2010, Excel 2013 and Mac Excel 2011 (see discussion in the web page). http://www.rondebruin.nl/win/addins/datepicker.htm  It may be easier to manage the references problem for this control than with the one that Microsoft furnishes.
0
 

Author Comment

by:BrainFrost
ID: 39777197
Hello Brad,

We've been using the form for a while now and love it.  However, updating the data in the tables used in the form is quite tedious and time consuming.  I've attached a word document that lists the steps involved.

Are there any shortcuts or step saving tips you can suggest?
0
 
LVL 80

Expert Comment

by:byundt
ID: 39777912
I've attached a word document that lists the steps involved.
There was no document attached. One common reason for this is failing to put descriptive text in the Description field after you have clicked the Upload button. If you leave that field blank, the attachment will be omitted when you click the Submit button.
0
 

Author Comment

by:BrainFrost
ID: 39778014
Thanks, I missed that.  One more try.
SvcCompletedFormUpdate.doc
0
 
LVL 80

Expert Comment

by:byundt
ID: 39787562
As an overarching comment, the written steps in SvcCompletedFormUpdate.doc are a good starting point for a set of macros that automate the entire task. The scope of work goes beyond what you might expect in a single Experts-Exchange question, but you could certainly break it up into a series of questions, or subcontract the work.

That said, one of the benefits of using Tables is the fact that they automatically grow and shrink with the amount of data. Except for cells X3 & Z11, the suggested formulas ought to adapt automatically to the changes in the underlying data.

For cells X3 and Z11, I got it to update automatically by changing the Data Validation sources to:
=State            (for cell X3)
=ddServiceCodeCategory        (for cell Z11)

The new named range ddServiceCodeCategory uses the Refers to:
=CodeCat[Category]

You should not need to repair named ranges or data validations. They should just work.

Brad
XLsvcFormTestQ28286411x.xlsx
0
 

Author Comment

by:BrainFrost
ID: 39829554
Thanks Brad.  I think the problems I have are created by how I'm getting new data into the tables when an update is needed.  The existing tables are not updated but replaced with new ones and the old ones get moved over to different columns in the process.  This moves the data validation ranges that refer to them.  Then I delete the old tables as they are no longer needed because new up-to-date tables have been created to replace them.

Can the new tables be imported so that they overwrite the existing tables?  If possible, I think this would save a lot of time making repairs after an update.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39829806
Tables are designed to be overwritten. You should definitely be pasting the new data on top of the original data in the tables. Don't insert new columns.

If you paste the new data in the first blank row underneath the table, Excel will append the new data into the original table. If you paste the new data in the first cell of the table, Excel will replace the old data with the new. If your new data includes header labels, paste it on top of the first header label in the original table.

Note that if the new data contains fewer rows than the original, you will want to shorten the original table before pasting the new data on top of it. To do this:
1.  Select enough data at the bottom of the table so that the remainder has fewer rows than the new data.
2.  While the excess data is still selected, right-click and then choose Clear contents from the resulting dialog
3.  On the Table Tools menu, choose Resize Table. Change the range in the resulting dialog includes the header row through the last row of remaining data.
4.  Paste your new data on top of the original
0
 

Author Comment

by:BrainFrost
ID: 39830181
I don't know how to do what you are suggesting.  I've been using the "Get External Data From Access" feature under the Data Tab, which does not allow me to put new data over existing data.  The feature is "grayed-out" unless an empty cell is selected.

I would love to learn an automated way to "update" or "replace" the existing tables with fresh ones from the Access database.  The database queries that create the newly updated tables (in the database) work well and are called from a macro, so the process is relatively quick and easy.  Getting the updated table data into the spreadsheet is the time-consuming part of the process.  Can this be done with a macro in Excel?  Is there a better way?
0
 
LVL 80

Expert Comment

by:byundt
ID: 39830261
I was copying from data that was already in Excel (such as in scratch space) rather than directly from Access.

Are you not able to Refresh the data?
"Refresh connected (imported) data" http://office.microsoft.com/en-us/excel-help/refresh-connected-imported-data-HP010087045.aspx?CTT=5&origin=HA010099664
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now