Exclude certain values from dropdown list

Polanskiman

Board Regular
Joined
Nov 29, 2011
Messages
119
Office Version
  1. 365
  2. 2016
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. MacOS
  3. Mobile
Hello,

I am working a rather complex worksheet. I'll try to make my question simple. Please let me know if you need further details.

I have a table with multiple rows/columns containing data. I need to create several dropdown lists from that table and from the same range but with each dropdown list excluding certain values according to certain criteria.

I cannot use VBA for this project.

Is that possible?
 
Last edited:
Starting with this one first. I do not use a Mac so I can't be any help specifically with that. The grammar of this point lost me a little but I think you are saying that you want the DV list in the table? If so, that is fine. Would you hide this column? If so, that would also hide what I have in A29 as the users do not need to see that. So I have moved the list formula to column A. You might consider simplifying the table heading in col A to something like 'ProductHelp' as that would make the A29 formula somewhat simpler.
Again thank you.

Ok got you. Appologies for the grammar. I am dyslexic and tend to forget words in sentences. I don't necessarily need the helper column within the table. In fact wanted to take it out of the table as you suggested to put it outside the table. In fact that entire sheet is not supposed to be seen by the end user. Only the dropdown list will be seen from another sheet. That sheet is merely used to extract data so I don't need to hide anything there. Just making it pleasing enough to see when we are working on it.

One note though. I placed the dropdown list in another sheet and nothing appears in it. :( I suppose it's due to the INDIRECT function.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Ok thanks. Just for me to understand why is that you said that the helper column would be best outside of the table boundaries?
 
Upvote 0
Not so much 'best' out of the table, but how I would do it. The normal basis of a table is that each row in the table is a row of related data. With the DV list in the table, that is not the case for that column.

Reversing the question. Why do you think it best in the table?

Should be doable either way in the end - your choice. :)
 
Upvote 0
The normal basis of a table is that each row in the table is a row of related data. With the DV list in the table, that is not the case for that column.
Ok that clears things up. I understand now.
Reversing the question. Why do you think it best in the table?
I had absolutely no idea really until you explained the basis of a table. You provided both solutions so now it's a matter of choice as you said but I suppose keeping the helper out of the table would probably be the most adequate thing to do. :) My question was really to try to understand how things work.

Again thanks for the support and explanation.

? from Thailand
 
Upvote 0
I guess that one advantage of having the DV list in the table like post #10 is that the formulas making the list and the A29 formula will always auto-adjust for any increase/decrease in table rows. For post #8 the P4 formula has to be copied down a sufficient number of rows to allow for any possible expansion, and the COUNTIF part of the P3 formula also needs to be sufficiently large. Both those points could become an issue if the table expands a lot and enough leeway has not been left.
 
Upvote 0
Ok this is what I did. All seems to be working. I just draged the formula to the right to create another helper column with a different criteria.

Book1
ABCDE
1
2$A$4:$A$22$B$4:$B$25
3Helper (fert+rem)Helper (fert+tr)ProductsType
4ADA Brighy 'N'ADA Brighy 'N'ADA Brighy 'N'fert
5ADA DosingADA DosingADA Dosingfert
6ADA E.I. MixADA E.I. MixADA E.I. Mixfert
7APFUK MacrosAPFUK (trace)APFUK (trace)tr
8Clives E.I. TargetAPFUK MacrosAPFUK Macrosfert
9EI Dosing FullClives E.I. TargetClives E.I. Targetfert
10EI Dosing LowCSM+B (Trace)CSM+B (Trace)tr
11EI Dosing MidEI Dosing FullEI Dosing Fullfert
12EI ModifiedEI Dosing LowEI Dosing Lowfert
13PMDDEI Dosing MidEI Dosing Midfert
14PMDD+PO4EI ModifiedEI Modifiedfert
15Proflora FerropolGLA EDTA Micromix (trace)GLA EDTA Micromix (trace)tr
16Seachem Equilibrium (remineraliser)PMDDPMDDfert
17Seachem FlourishPMDD+PO4PMDD+PO4fert
18TNC CompleteProflora FerropolProflora Ferropolfert
19TNC GH Boost (remineraliser)Rexolin APN (trace)Rexolin APN (trace)tr
20TPN+ FertiliserSeachem FlourishSeachem Equilibrium (remineraliser)rem
21Tropica Premium NutritionSolufeed TEC (trace)Seachem Flourishfert
22Tropica Specialised NutritionTNC CompleteSolufeed TEC (trace)tr
23 TPN+ FertiliserTNC Completefert
24 Tropica Premium NutritionTNC GH Boost (remineraliser)rem
25 Tropica Specialised NutritionTPN+ Fertiliserfert
26  Tropica Premium Nutritionfert
27  Tropica Specialised Nutritionfert
28
29EI ModifiedADA Brighy 'N'
30
RCFerts
Cell Formulas
RangeFormula
A2A2=CELL("address",INDEX(RCFerts_Table[Helper (fert+rem)],1))&":"&CELL("address",OFFSET(INDEX(RCFerts_Table[Helper (fert+rem)],1),COUNTIF(RCFerts_Table[Helper (fert+rem)],"?*")-1,,1))
B2B2=CELL("address",INDEX(RCFerts_Table[Helper (fert+tr)],1))&":"&CELL("address",OFFSET(INDEX(RCFerts_Table[Helper (fert+tr)],1),COUNTIF(RCFerts_Table[Helper (fert+tr)],"?*")-1,,1))
A4:A27A4=IFERROR(INDEX([Products],AGGREGATE(15,6,(ROW([Products])-ROW(RCFerts_Table[#Headers]))/(([Type]="fert")+([Type]="rem")),ROWS(A$4:A4))),"")
B4:B27B4=IFERROR(INDEX([Products],AGGREGATE(15,6,(ROW([Products])-ROW(RCFerts_Table[#Headers]))/(([Type]="fert")+([Type]="tr")),ROWS(A$4:A4))),"")
Cells with Data Validation
CellAllowCriteria
A29List=INDIRECT("RCFerts!"&$A$2)
B29List=INDIRECT("RCFerts!"&$B$2)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top