Excel comboboxes and filtering.

Can you post your workbook?

I can't, it has sensitive information in it. :/

Can you dummy something up that reflects the problem? Otherwise it will be difficult to help.

Hope this helps.  :)
dummy.xlsx

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."?

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.

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

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.

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":


Selection of "2":


Selection of "3":


Finally, selection of "4":

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.

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])?

No, Combo2 has no bearing on the selection.

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.

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

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)

Thanks.

I think I just beat you to it! :)

Please can you review the workbook I posted just above your last comment?

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.

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. :)

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?

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...

Perfect! Thank you so much.

You're very welcome.

Sorry it took so long.

I should learn to read! :)

Thanks for closing the question.

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?

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

Worked like a charm. You're brilliant :)

:) Just happy to help.

Thanks for making the suggestion for improvement in any respect.