Greenbehindthecells
Board Regular
- Joined
- May 9, 2023
- Messages
- 50
- Office Version
- 365
- Platform
- Windows
Hello everyone,
I hope you are well. I have a combobox that is not allowing the selection of a shorter, similarly named but different value... For my purposes, my combobox needs to search for 'ABC' practice... however, possibly due to the generic names of my other practices (ABC GI, ABC Neph, ABC Card, etc) and the formula I used (sort/filter), the combobox does not allow the selection of 'ABC' practice (my sort\filter formula will return the result of all of ABC practices despite any clicking in the combobox of just 'ABC' practice). The formula works for all other practices with longer more distinct value/names (ABC GI, ABC Neph, ABC Card, etc) with no issues.
While I see that this is an issue with the formula in my List tab used to sort/filter the search, I cant figure out a better formula to use for this purpose to get the result in my combobox that when 'ABC' is entered, and it 'ABC' is selected as confirmation, it will populate 'ABC'. I can't change the values/names (for example adding a =char(185) to make the practice names unique because in my real data, I have hundreds of providers for a generic practice name like this for specific locations.
I recreated the original workbook with the settings & VBA I used from various tutorials and added to my post. My data sheet was too large for mini excel so I added a shortened version and a screenshot, hoping to give an idea of the data that should be populated in the combobox. Any assistance would be greatly appreciated. Thank you for your time.
Screenshot of combobox not working with 'ABC' practice name:
Combobox working with all other longer names/values:
Data sheet:
VBA code used for combobox:
Private sub Tempbox 4_change ()
Tempbox 4.listfillrange = "Dropdown1"
Me.Tempbox4.Dropdown
End Sub
List sheet with search formula linked to d2/Combobox in Practice Search v2 sheet:
Practice Details V2 (Combobox) sheet
Combobox properties:
I hope you are well. I have a combobox that is not allowing the selection of a shorter, similarly named but different value... For my purposes, my combobox needs to search for 'ABC' practice... however, possibly due to the generic names of my other practices (ABC GI, ABC Neph, ABC Card, etc) and the formula I used (sort/filter), the combobox does not allow the selection of 'ABC' practice (my sort\filter formula will return the result of all of ABC practices despite any clicking in the combobox of just 'ABC' practice). The formula works for all other practices with longer more distinct value/names (ABC GI, ABC Neph, ABC Card, etc) with no issues.
While I see that this is an issue with the formula in my List tab used to sort/filter the search, I cant figure out a better formula to use for this purpose to get the result in my combobox that when 'ABC' is entered, and it 'ABC' is selected as confirmation, it will populate 'ABC'. I can't change the values/names (for example adding a =char(185) to make the practice names unique because in my real data, I have hundreds of providers for a generic practice name like this for specific locations.
I recreated the original workbook with the settings & VBA I used from various tutorials and added to my post. My data sheet was too large for mini excel so I added a shortened version and a screenshot, hoping to give an idea of the data that should be populated in the combobox. Any assistance would be greatly appreciated. Thank you for your time.
Screenshot of combobox not working with 'ABC' practice name:
Combobox working with all other longer names/values:
Data sheet:
Combobox.xlsm | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
1 | GROUP NAME | TAX ID (TAB REPORT) | NPI | PRINT NAME | ADDRESS3 | ADDRESS1 | ADDRESS2 | CITY | STATE | ZIP | COUNTY | PHONE | FAX (TAB REP MATCH) | LANG CONSOL | HOURS CONSOL | PCP | ||
2 | ABC | 999999999 | 1E+10 | NIEVES, JESUS, DO | 11 BROADWAY, STE 11 | 11 BROADWAY | STE 11 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (999) 999-9991 | (999) 999-9992 | GREEK, MANDARIN | MON: 8:00AM TO 5PM, TUES: 8:00AM TO 5PM, WED: 8:00AM TO 5PM, THURS: 8:00AM TO 5PM, FRI: 8:00AM TO 5PM, SAT: CLOSED, SUN: CLOSED | y | ||
3 | ABC PEDS | 999999999 | 1E+10 | SMITH, JOHN, DO | 11 BROADWAY, STE 22 | 11 BROADWAY | STE 22 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (999) 999-9993 | (999) 999-9994 | Not on file | Not on file | n | ||
4 | ABC GI | 999999999 | 1E+10 | CARTER, CARTER, DO | 10 BROADWAY, STE 11 | 10 BROADWAY | STE 11 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (999) 999-9995 | (999) 999-9996 | SPANISH | MON: 8:00AM TO 5PM, TUES: 8:00AM TO 5PM, WED: 8:00AM TO 5PM, THURS: 8:00AM TO 5PM, FRI: 8:00AM TO 5PM, SAT: CLOSED, SUN: CLOSED | n | ||
5 | ABC CARD | 999999999 | 1E+10 | SHIN, XE, DO | 10 BROADWAY, STE 22 | 10 BROADWAY | STE 22 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (999) 999-9997 | (999) 999-9998 | RUSSIAN | MON: 8:00AM TO 5PM, TUES: 8:00AM TO 5PM, WED: 8:00AM TO 5PM, THURS: 8:00AM TO 5PM, FRI: 8:00AM TO 5PM, SAT: 8:00AM TO 5PM, SUN: CLOSED | n | ||
6 | ABC NEPH | 999999999 | 1E+10 | OHNSON, LISA, DO | 9 BROADWAY, STE 1 | 9 BROADWAY | STE 1 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (999) 999-9999 | (999) 999-9910 | POLISH, PORTEGUESE | MON: 8:00AM TO 5PM, TUES: 8:00AM TO 5PM, WED: 8:00AM TO 5PM, THURS: CLOSED, FRI: 8:00AM TO 5PM, SAT: 8:00AM TO 5PM, SUN: CLOSED | n | ||
7 | BBC | 888888888 | 1E+10 | WILLIAMS, MICHAEL, MD | 11 BROADWAY, STE 1 | 11 BROADWAY | STE 1 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0001 | (999) 999-9930 | TELUGO | Not on file | y | ||
8 | BBC PEDS | 888888888 | 1E+10 | JONES, SARAH, DO | 11 BROADWAY, STE 2 | 11 BROADWAY | STE 2 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0002 | (999) 999-9914 | HAITIAN | Not on file | n | ||
9 | BBC GI | 888888888 | 1E+10 | BROWN, ROBERT, MD | 10 BROADWAY, STE 3 | 10 BROADWAY | STE 3 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0003 | (999) 999-9898 | DUTCH | MON: 8:00AM TO 5PM, TUES: 8:00AM TO 5PM, WED: 8:00AM TO 5PM, THURS: 8:00AM TO 5PM, FRI: 8:00AM TO 5PM, SAT: CLOSED, SUN: CLOSED | n | ||
10 | BBC CARD | 888888888 | 1E+10 | DAVIS, MARY, DO | 10 BROADWAY, STE 4 | 10 BROADWAY | STE 4 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0004 | (999) 999-9882 | GREEK, MANDARIN | MON: 8:00AM TO 5PM, TUES: 8:00AM TO 5PM, WED: 8:00AM TO 5PM, THURS: 8:00AM TO 5PM, FRI: 8:00AM TO 5PM, SAT: 8:00AM TO 5PM, SUN: CLOSED | n | ||
11 | BBC NEPH | 888888888 | 1E+10 | MILLER, JAMES, MD | 9 BROADWAY, STE 5 | 9 BROADWAY | STE 5 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0005 | (999) 999-9866 | Not on file | MON: 8:00AM TO 5PM, TUES: 8:00AM TO 5PM, WED: 8:00AM TO 5PM, THURS: CLOSED, FRI: 8:00AM TO 5PM, SAT: 8:00AM TO 5PM, SUN: CLOSED | n | ||
12 | CBC | 777777777 | 1E+10 | WILSON, JENNIFER, MD | 22 BROADWAY, STE 101 | 22 BROADWAY | STE 101 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0006 | (999) 999-9862 | SPANISH | Not on file | y | ||
13 | CBC PEDS | 777777777 | 1E+10 | MOORE, DAVID, DO | 22 BROADWAY, STE 102 | 22 BROADWAY | STE 102 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0007 | (999) 999-9851 | RUSSIAN | Not on file | n | ||
14 | CBC GI | 777777777 | 1E+10 | TAYLOR, LINDA, MD | 33 BROADWAY, STE 3 | 33 BROADWAY | STE 3 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0008 | (999) 999-9840 | POLISH, PORTEGUESE | MON: 8:00AM TO 5PM, TUES: 8:00AM TO 5PM, WED: 8:00AM TO 5PM, THURS: 8:00AM TO 5PM, FRI: 8:00AM TO 5PM, SAT: CLOSED, SUN: CLOSED | n | ||
15 | CBC CARD | 777777777 | 1E+10 | ANDERSON, MARK, MD | 44 BROADWAY, STE 96 | 44 BROADWAY | STE 96 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0009 | (999) 999-9829 | TELUGO | MON: 8:00AM TO 5PM, TUES: 8:00AM TO 5PM, WED: 8:00AM TO 5PM, THURS: 8:00AM TO 5PM, FRI: 8:00AM TO 5PM, SAT: 8:00AM TO 5PM, SUN: CLOSED | n | ||
16 | CBC NEPH | 777777777 | 1E+10 | THOMAS, ELIZABETH, DO | 55 BROADWAY, STE 195 | 55 BROADWAY | STE 195 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0010 | (999) 999-9819 | HAITIAN | MON: 8:00AM TO 5PM, TUES: 8:00AM TO 5PM, WED: 8:00AM TO 5PM, THURS: CLOSED, FRI: 8:00AM TO 5PM, SAT: 8:00AM TO 5PM, SUN: CLOSED | n | ||
17 | DBC | 666666666 | 1E+10 | JACKSON, CHARLES, MD | 33 BROADWAY, STE 11 | 33 BROADWAY | STE 11 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0011 | (999) 999-9808 | DUTCH | Not on file | y | ||
18 | DBC PEDS | 666666666 | 1E+10 | WHITE, MARIA, MD | 33 BROADWAY, STE 12 | 33 BROADWAY | STE 12 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0012 | (999) 999-9797 | GREEK, MANDARIN | Not on file | n | ||
19 | DBC GI | 666666666 | 1E+10 | HARRIS, MATTHEW, DO | 33 BROADWAY, STE 3 | 33 BROADWAY | STE 3 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0013 | (999) 999-9786 | Not on file | MON: 8:00AM TO 5PM, TUES: 8:00AM TO 5PM, WED: 8:00AM TO 5PM, THURS: 8:00AM TO 5PM, FRI: 8:00AM TO 5PM, SAT: CLOSED, SUN: CLOSED | n | ||
20 | DBC CARD | 666666666 | 1E+10 | MARTIN, SUSAN, MD | 33 BROADWAY, STE 6 | 33 BROADWAY | STE 6 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0014 | (999) 999-9775 | SPANISH | MON: 8:00AM TO 5PM, TUES: 8:00AM TO 5PM, WED: 8:00AM TO 5PM, THURS: 8:00AM TO 5PM, FRI: 8:00AM TO 5PM, SAT: 8:00AM TO 5PM, SUN: CLOSED | n | ||
21 | DBC NEPH | 666666666 | 1E+10 | THOMPSON, PAUL, MD | 33 BROADWAY, STE 15 | 33 BROADWAY | STE 15 | WILMINGTON | DE | 19801 | NEW CASTLE (F) | (110) 000-0015 | (999) 999-9764 | RUSSIAN | MON: 8:00AM TO 5PM, TUES: 8:00AM TO 5PM, WED: 8:00AM TO 5PM, THURS: CLOSED, FRI: 8:00AM TO 5PM, SAT: 8:00AM TO 5PM, SUN: CLOSED | n | ||
DD SS NN FF Main |
Cell Formulas | ||
---|---|---|
Range | Formula | |
N2:N21 | N2 | =IF(AND([@LANG1]=0,[@LANG2]=0,[@LANG3]=0,[@LANG4]=0,[@LANG5]=0,[@LANG6]=0),"Not on file",TEXTJOIN(", ",TRUE,NewPract[@[LANG1]:[LANG6]])) |
O2:O21 | O2 | =IF(AND( [@MON]=0,[@TUE]=0,[@WED]=0,[@THUR]=0,[@FRI]=0,[@SAT]=0,[@SUN]=0), "Not on file",TEXTJOIN(", ",TRUE,"MON: "&[@MON], "TUES: "&[@TUE],"WED: "&[@WED],"THURS: "&[@THUR],"FRI: "&[@FRI],"SAT: "&[@SAT], "SUN: "&[@SUN])) |
E2:E21 | E2 | =TEXTJOIN(", ",TRUE,[@ADDRESS1],[@ADDRESS2]) |
VBA code used for combobox:
Private sub Tempbox 4_change ()
Tempbox 4.listfillrange = "Dropdown1"
Me.Tempbox4.Dropdown
End Sub
List sheet with search formula linked to d2/Combobox in Practice Search v2 sheet:
Combobox.xlsm | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Response | Pratices | Searchable | |||||||||
2 | Yes | ABC | ABC | |||||||||
3 | No | ABC PEDS | ABC CARD | |||||||||
4 | Unkown | ABC GI | ABC GI | |||||||||
5 | ABC CARD | ABC NEPH | ||||||||||
6 | ABC NEPH | ABC PEDS | ||||||||||
7 | BBC | |||||||||||
8 | BBC PEDS | |||||||||||
9 | BBC GI | |||||||||||
10 | PCPPaps | BBC CARD | ||||||||||
11 | Yes | BBC NEPH | ||||||||||
12 | No | CBC | ||||||||||
13 | Unkown | CBC PEDS | ||||||||||
14 | Specific Provider Only | CBC GI | ||||||||||
15 | CBC CARD | |||||||||||
16 | CBC NEPH | |||||||||||
17 | DBC | |||||||||||
18 | DBC PEDS | |||||||||||
19 | DBC GI | |||||||||||
20 | DBC CARD | |||||||||||
21 | DBC NEPH | |||||||||||
22 | ||||||||||||
23 | ||||||||||||
24 | ||||||||||||
25 | ||||||||||||
List |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E6 | E2 | =SORT(FILTER(Practices6[#Data],ISNUMBER(SEARCH('Practice Details V2'!$D$2,Practices6[#Data])),"Not found")) |
Dynamic array formulas. |
Practice Details V2 (Combobox) sheet
Combobox.xlsm | ||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | I | J | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |||||||
1 | Contact | Hours | ||||||||||||||||||||||||||
2 | abc | formula from other sheet | MON: 8:00AM TO 5PM, TUES: 8:00AM TO 5PM, WED: 8:00AM TO 5PM, THURS: 8:00AM TO 5PM, FRI: 8:00AM TO 5PM, SAT: CLOSED, SUN: CLOSED | |||||||||||||||||||||||||
3 | ||||||||||||||||||||||||||||
4 | ||||||||||||||||||||||||||||
5 | ||||||||||||||||||||||||||||
6 | Tax | NPI | Prov | Addresses | City | State | Phone | Fax | ||||||||||||||||||||
7 | make a selection | make selection in field | make selection in field | County Health Dept | ||||||||||||||||||||||||
8 | formula from other sheet | |||||||||||||||||||||||||||
9 | ||||||||||||||||||||||||||||
10 | ||||||||||||||||||||||||||||
11 | ||||||||||||||||||||||||||||
12 | ||||||||||||||||||||||||||||
13 | ||||||||||||||||||||||||||||
14 | Misc Notes | |||||||||||||||||||||||||||
15 | formula from other sheet | |||||||||||||||||||||||||||
16 | ||||||||||||||||||||||||||||
17 | ||||||||||||||||||||||||||||
18 | ||||||||||||||||||||||||||||
19 | P? | POC? | ||||||||||||||||||||||||||
20 | formula from other sheet | formula from other sheet | ||||||||||||||||||||||||||
21 | ||||||||||||||||||||||||||||
22 | ||||||||||||||||||||||||||||
23 | ||||||||||||||||||||||||||||
24 | Languages | Acron | ||||||||||||||||||||||||||
25 | make Selection in field | Make selection field | ||||||||||||||||||||||||||
26 | ||||||||||||||||||||||||||||
27 | ||||||||||||||||||||||||||||
28 | ||||||||||||||||||||||||||||
29 | ||||||||||||||||||||||||||||
30 | ||||||||||||||||||||||||||||
31 | ||||||||||||||||||||||||||||
32 | ||||||||||||||||||||||||||||
33 | ||||||||||||||||||||||||||||
Practice Details V2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
N2 | N2 | =IFERROR(INDEX(NewPract[#Data],MATCH($D$2,NewPract[GROUP NAME],0),15)," ") |
B7 | B7 | =IFERROR(FILTER(UNIQUE(IF(FILTER(NewPract[TAX ID (TAB REPORT)],EXACT(NewPract[GROUP NAME],$D$2))=0,"No Data",FILTER(NewPract[TAX ID (TAB REPORT)],EXACT(NewPract[GROUP NAME],$D$2)))),UNIQUE(IF(FILTER(NewPract[TAX ID (TAB REPORT)],EXACT(NewPract[GROUP NAME],$D$2))=0,"No Data",FILTER(NewPract[TAX ID (TAB REPORT)],EXACT(NewPract[GROUP NAME],$D$2))))<>""),"make a selection") |
C7 | C7 | =IFERROR(UNIQUE(IF(FILTER(NewPract[NPI],EXACT(NewPract[GROUP NAME],$D$2))=0,"no data available",FILTER(NewPract[[NPI]:[PRINT NAME]],EXACT(NewPract[GROUP NAME],$D$2)))),"make selection in field") |
F7 | F7 | =IFERROR(UNIQUE(IF(FILTER(NewPract[ADDRESS3],EXACT(NewPract[GROUP NAME],$D$2))=0,"No data avialable",FILTER(NewPract[[ADDRESS3]:[FAX (TAB REP MATCH)]],EXACT(NewPract[GROUP NAME],$D$2)))),"make selection in field") |
P25 | P25 | =FILTER(IFERROR(UNIQUE(IF(FILTER(NewPract[LANG CONSOL],EXACT(NewPract[GROUP NAME],$D$2))=0,"not on file",FILTER(NewPract[LANG CONSOL],EXACT(NewPract[GROUP NAME],$D$2)))),"make Selection in field"),IFERROR(UNIQUE(IF(FILTER(NewPract[LANG CONSOL],EXACT(NewPract[GROUP NAME],$D$2))=0,"not on file",FILTER(NewPract[LANG CONSOL],EXACT(NewPract[GROUP NAME],$D$2)))),"make Selection in field")<>"") |
R25 | R25 | =IFERROR(UNIQUE(IF(FILTER(NewPract[LOB],EXACT(NewPract[GROUP NAME],$D$2))=0,"No data availabe",FILTER(NewPract[LOB],EXACT(NewPract[GROUP NAME],$D$2)))),"Make selection field") |
Combobox properties: