First of all I would like to thank you in advance for taking the time to read my question, I have looked everywhere for a solution with no avail, without further ado, here is the problem:
Suppose we have three columns as below: (I am using Excel 2010)
As observed from the above, all columns may contain duplicates, the goal is to have three cells in a different sheet where data validation lists would be used. A typical scenario would be:
User selects the ID (e.g. 2), then on the next cell he selects the Sys (e.g. Roma), this list would be filtered according to the ID selected on the previous cell (only "Roma" and "Lima" shown), lastly, he selects the Code, this would be filtered according to the Sys selected (only "A5" and "A7" shown).
The approach which I am currently using is to have a separate list of only unique IDs, this is being used on the first validation list, then on the second validation list I am using the below formula to select the respective Sys:
<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">=OFFSET(IDS_Start,MATCH(A1,IDS,0)-2,1,COUNTIF(IDS,A1),1
</code>
Where IDS_Start is a reference to the first ID in my list, IDS a reference to the entire column ID and A1 is the cell in which the user selects the ID.
The problem with the above is that it will result in duplicates to be displayed on the second validation list, is there a way around this ? A helping hand would be much appreciated.
Suppose we have three columns as below: (I am using Excel 2010)
Code:
ID Sys Code
1 Roma A4
2 Roma A5
2 Roma A5
2 Roma A7
2 Lima A7
3 Lima B1
4 Lima C1
4 Lima D3
4 Lima D5
5 Alpha E9
6 Alpha E2As observed from the above, all columns may contain duplicates, the goal is to have three cells in a different sheet where data validation lists would be used. A typical scenario would be:
User selects the ID (e.g. 2), then on the next cell he selects the Sys (e.g. Roma), this list would be filtered according to the ID selected on the previous cell (only "Roma" and "Lima" shown), lastly, he selects the Code, this would be filtered according to the Sys selected (only "A5" and "A7" shown).
The approach which I am currently using is to have a separate list of only unique IDs, this is being used on the first validation list, then on the second validation list I am using the below formula to select the respective Sys:
<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">=OFFSET(IDS_Start,MATCH(A1,IDS,0)-2,1,COUNTIF(IDS,A1),1
</code>
Where IDS_Start is a reference to the first ID in my list, IDS a reference to the entire column ID and A1 is the cell in which the user selects the ID.
The problem with the above is that it will result in duplicates to be displayed on the second validation list, is there a way around this ? A helping hand would be much appreciated.
Last edited:
