Excel comboboxes and filtering.

I have an excel spreadsheet I am trying to build (Normally I work with Access/VBA).

On my main page I have an ActiveX combo box whose source is a list that is placename and number.
   I've figured out how to display the placenames in the combo box and reflect the # in a cell.
I have a list on a 2nd page that contains various details and a placename number that matches the data above.
On my main page I have ?? cell imbedded combobox using data validation using the above list as a source.

What I would like to do is filter that list/source based on the number selected in the activex combobox.
Can I do this w/o adding a module?
If not, how do I do this :)
Abbie OffmanComputer Services OfficerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Can you post your workbook?
Abbie OffmanComputer Services OfficerAuthor Commented:
I can't, it has sensitive information in it. :/
Martin LissOlder than dirtCommented:
Can you dummy something up that reflects the problem? Otherwise it will be difficult to help.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Abbie OffmanComputer Services OfficerAuthor Commented:
Hope this helps.  :)
dummy.xlsx
Martin LissOlder than dirtCommented:
OK I see that your Combobox1's source is DHAList and that Combobox2's source is the QTR sheet.

Some questions:

What does "...and reflect the # in a cell." mean?
When you say "I have a list on a 2nd page", which sheet are you referring to?
When you say "On my main page I have ?? cell imbedded combobox" what does the "??" represent?
And can you give an example of "What I would like to do is filter that list/source based on the number selected in the activex combobox."?
Abbie OffmanComputer Services OfficerAuthor Commented:
What I'm trying to do:

If they select County 1 in combobox 1(source is dha list) I would like the embedded combo box in Column A (source rota list) to display a list of specialties with a dha_id of 1.
[ fanpages ]IT Services ConsultantCommented:
Hi,

I have added two columns to the worksheet [ROTA LIST].

Column [ I ] has a named range, [BlanksRange], defined within it: [='ROTA LIST'!$I$2:$I$132].
Column [ J ] has a named range, [NoBlanksRange], defined as [='ROTA LIST'!$J$2:$J$13].

[BlanksRange] extends down the rows (from 2) to 132; the last row where data exists in column [ A ].
[NoBlanksRange] extends down only 10 rows (from row 2); as 10 is the maximum number of entries per "DHA_ID".

Cell [I2] contains a formula: =IF(D2=VALUE(Sheet1!C$2),A2,"")

This is copied from Cell [I2] down to Cell [I132].

The range [J2:J13] has the array formula:

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

(This is documented on Chip Pearson's site: [ http://www.cpearson.com/excel/NoBlanks.aspx ]).

Finally, I changed the Data Validation in cell [A12] of worksheet [Sheet1]...

From:
='ROTA LIST'!$A$2:$A$132

To:
=NoBlanksRange

Cell [A12] will need to be copied down column [ A ] as far as required.

An updated workbook is attached for your review.

PS. The only issue I can see with this approach is that if an entry is selected in, say, cell [A12] of [Sheet1], & the drop-down (combo-box) list for "DHA_ID" is changed to a number of a list that does not contain the selected value (presently in cell [A12]), then there is the potential that an invalid entry is recorded for the new "DHA_ID" selected.
Q-28708444.xlsx
Abbie OffmanComputer Services OfficerAuthor Commented:
Hmm, Not sure it's working.

The drop down list in column A is the same no matter which County/DHA I select from combo1.
[ fanpages ]IT Services ConsultantCommented:
I see different results (although the values for "Specialty" (column [A] of [ROTA LIST]) are very similar.

With "DHA_ID" drop-down list selection of "1":

DHA_ID_1
Selection of "2":

DHA_ID_2
Selection of "3":

DHA_ID_3
Finally, selection of "4":

DHA_ID_4
[ fanpages ]IT Services ConsultantCommented:
The four lists (seen in the Data Validation drop-down selection in cell [A12] only) are as follows:

"1"
Anesthesia
General Surgery
Internal Medicine
Family Medicine/Physician
Diagnostic Radiology
Obstetrics
Obstetrics/Gynecology
Psychiatry (Adult)
Pathology

"2"
Anesthesia
General Surgery
Internal Medicine
Family Medicine/Physician (Obstetrics)
Family Medicine/Physician
Diagnostic Radiology
Obstetrics
Pediatrics
Psychiatry (Adult)
Ophthalmology
Pathology

"3"
Anesthesia
General Surgery
Internal Medicine
Family Medicine/Physician
Diagnostic Radiology
Obstetrics
Orthopedics
Family Medicine/Physician (Surgical Assists & Obstetrics)
Pediatrics
Psychiatry (Adult)
Otolaryngology

"4"
Anesthesia
General Surgery
Internal Medicine
Family Medicine/Physician
Diagnostic Radiology
Pediatrics
Obstetrics/Gynecology
Psychiatry (Adult)
Urology
Ophthalmology
Pathology

All four are (slightly) different to each other.
[ fanpages ]IT Services ConsultantCommented:
Hmm, Not sure it's working.

The drop down list in column A is the same no matter which County/DHA I select from combo1.

Oh, hang on...

I used "ComboBox2" (the drop-down list that contains 1, 2, 3, & 4).

Was this not correct?  Ah... I see now that these are "Quarters" (perhaps), not "DHA_ID" values.


How do the values in "ComboBox1" influence what is shown in cell [A12]?

Does the selection of one of...

County 1
County 2
County 3
County 4
County 5
County 6
County 7
County 8
County 9
County 10

Then lead to (via the [DHA LIST] worksheet) which DHA (1 to 10) to use, not the value selected in "ComboBox2"?

Does "ComboBox2" not have any bearing on the Data Validation list contents (in cell [C12])?
Abbie OffmanComputer Services OfficerAuthor Commented:
No, Combo2 has no bearing on the selection.
[ fanpages ]IT Services ConsultantCommented:
Please confirm what you expect to see in cell [A12] if "County 1", "County 2", & also "County 10" is selected, so that you state the values for each of the three selections, & I will adjust accordingly.

Sorry if I missed on the first attempt.
[ fanpages ]IT Services ConsultantCommented:
I have the feeling I just need to change cell [I2] in the [ROTA LIST] worksheet to:
=IF(D2=VALUE(Sheet1!C$1),A2,"")

Copy this down column [ I ] to cell [I132], & extend the array formula in column [ J ] to take account of the longer lists that are now possible.

I have attached an updated workbook.


However, see rows 26 & 27 in your [ROTA LIST] worksheet.

Is that duplication intentional?  ("Internal Medicine" / 8)

Rows 40 & 41 are also duplicates ("Family Medicine/Physician" / 9).
Q-28708444b.xlsx
Abbie OffmanComputer Services OfficerAuthor Commented:
If you select County 1 you should see:
Anesthesia
General Surgery
Internal Medicine
Family Medicine/Physician
Diagnostic Radiology
Obstetrics
Obstetrics/Gynecology
Psychiatry (Adult)
Pathology

County 2
Anesthesia
General Surgery
Internal Medicine
Family Medicine/Physician (Obstetrics)
Family Medicine/Physician
Diagnostic Radiology
Obstetrics
Pediatrics
Psychiatry (Adult)
Ophthalmology
Pathology

County 10
Family Medicine/Physician
Obstetrics         (intentional duplication)
Obstetrics
Maternal  (Fetal)
Pediatric Nephrology
Gynecology
Family Medicine (Normal Newborn)
[ fanpages ]IT Services ConsultantCommented:
Thanks.

I think I just beat you to it! :)

Please can you review the workbook I posted just above your last comment?
Abbie OffmanComputer Services OfficerAuthor Commented:
Yay, we're on the way!
2 questions...

Not all of the specialties are in BlanksRange and NoBlanksRange. Can I just add those? And how do I extend the 'name' of the range to cover those additions.
Abbie OffmanComputer Services OfficerAuthor Commented:
Disregard that question...the lightbulb just went on. The blanks range and no blanks range change depending on the county section on sheet1. This is brilliant. :)
[ fanpages ]IT Services ConsultantCommented:
What do you mean by not all "Specialities" are in [BlanksRange] & [NoBlanksRange]?

Do you mean that you wish to add rows to the [ROTA LIST] worksheet (i.e. additional rows beyond row 132), as not all intended rows were included in your sample workbook?
Abbie OffmanComputer Services OfficerAuthor Commented:
See my comment above. :)

I feel like the issue now is that the dropdown on sheet1 Col A is limited to 12 choices. Trying to figure out that...
[ fanpages ]IT Services ConsultantCommented:
[ https://support.office.com/en-US/article/insert-or-delete-a-drop-down-list-fdde2fb6-e257-4c62-b794-1427762428c6 ]

"The maximum number of entries that you can have in a drop-down list is 32,767."

Sorry, the restriction to 12 is my fault.

I did not extend the named range [NoBlanksRange] beyond row 13 (as I originally had it set in the first revision of the workbook).

='ROTA LIST'!$J$2:$J$13

I have revised that now to...

='ROTA LIST'!$J$2:$J$29


Another version of the workbook is attached.
Q-28708444c.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Abbie OffmanComputer Services OfficerAuthor Commented:
Perfect! Thank you so much.
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.

Sorry it took so long.

I should learn to read! :)

Thanks for closing the question.
Abbie OffmanComputer Services OfficerAuthor Commented:
Fanpages,  I'm having a bit of an issue with the list.  Because the NoBlanksRange has blank lines at the bottom, the listbox is allowing text to be typed in that is not in the list.  Is there a way around this? LIke set the range of NoBlanksList dynamically?
[ fanpages ]IT Services ConsultantCommented:
Hi,

If I understand what you mean correctly...

I have created a new named range, [SelectionRange], defined as follows:
=OFFSET('ROTA LIST'!$J$2,0,0,COUNTA('ROTA LIST'!$J$2:$J$29)-COUNTBLANK('ROTA LIST'!$J$2:$J$29),1)

I have changed the Data Validation of cell [A12] of worksheet [Sheet1] to this new range.

Please find an updated workbook attached.
Q-28708444d.xlsx
Abbie OffmanComputer Services OfficerAuthor Commented:
Worked like a charm. You're brilliant :)
[ fanpages ]IT Services ConsultantCommented:
:) Just happy to help.

Thanks for making the suggestion for improvement in any respect.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.