Removing duplicate entries from a data validated drop down list in a three tier Cascading List.

[AirportAndy71]

I've managed to create a spreadsheet for subcontractors (i work in construction) to auto generate document numbers from 18 mandatory metadata fields (Only 8 fields fill the Document number but the others are mandatory to the EDMS system we use (Aconex)).

One section is looking for specific codes to the job and has three child cascading levels after the first is picked and obviously the 2nd field drop-down depends on the parent (1st Field) and the 3rd field drop-down depends on the 2nd field drop-down and the 4th field drop-down depends on the 3rd field drop-down.

I have a hidden sheet with all the valid metadata on it and the data validation formulas i've used to look for these once the field above has been chosen are as follows:

=OFFSET(AFC!$B$1,MATCH(L2,AFC!$B:$B,0)-1,1,COUNTIF(AFC!$B:$B,L2),1)
=OFFSET(AFC!$C$1,MATCH(M2,AFC!$C:$C,0)-1,1,COUNTIF(AFC!$C:$C,M2),1)
=OFFSET(AFC!$D$1,MATCH(N2,AFC!$D:$D,0)-1,1,COUNTIF(AFC!$D:$D,N2),1)

My problem is the cascading sheets have duplicates because each field chosen has multiple choices and each of those choices have again multiple choices and so on down the line.

And in the drop downs for the 2nd, 3rd & 4th in the series show these and id rather they just showed a single instance of each. I cant remove duplicates from the original lists as i need the formulas above to look for multiple entries and show whats related once chosen (Hence the COUNTIF).

Is there a way to have the drop downs not show multiples?

This is a small example of what my hidden data looks like for reference:

[TABLE="width: 1242"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]AFC[/TD]
[TD]selectList5[/TD]
[TD]selectList6[/TD]
[TD]selectList7[/TD]
[/TR]
[TR]
[TD]100 Asset Function Group[/TD]
[TD]100 Asset Function Group[/TD]
[TD]100 General Asset[/TD]
[TD]000 N/A[/TD]
[/TR]
[TR]
[TD]200 Building Structure & Fabric[/TD]
[TD]100 Asset Function Group[/TD]
[TD]118 Navaids[/TD]
[TD]000 N/A[/TD]
[/TR]
[TR]
[TD]300 Baggage[/TD]
[TD]100 Asset Function Group[/TD]
[TD]118 Navaids[/TD]
[TD]140 Control Panel[/TD]
[/TR]
[TR]
[TD]400 Safety Security & Fire[/TD]
[TD]100 Asset Function Group[/TD]
[TD]118 Navaids[/TD]
[TD]222 Monitoring Device[/TD]
[/TR]
[TR]
[TD]500 Information & Communications[/TD]
[TD]100 Asset Function Group[/TD]
[TD]118 Navaids[/TD]
[TD]222 Monitoring Device[/TD]
[/TR]
[TR]
[TD]600 People Related Transport[/TD]
[TD]100 Asset Function Group[/TD]
[TD]118 Navaids[/TD]
[TD]259 Approach Radar[/TD]
[/TR]
[TR]
[TD]700 Areas, Railways. Vehicles & Intangibles[/TD]
[TD]100 Asset Function Group[/TD]
[TD]118 Navaids[/TD]
[TD]303 Instrument Landing Sys ILS[/TD]
[/TR]
[TR]
[TD]800 Services & Utilities[/TD]
[TD]100 Asset Function Group[/TD]
[TD]118 Navaids[/TD]
[TD]307 Instrumented Runway Visual Range IRVR[/TD]
[/TR]
[TR]
[TD]900 Project Management & Controls[/TD]
[TD]100 Asset Function Group[/TD]
[TD]118 Navaids[/TD]
[TD]325 Application Software[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100 Asset Function Group[/TD]
[TD]118 Navaids[/TD]
[TD]326 Microwave Landing Sys MLS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100 Asset Function Group[/TD]
[TD]118 Navaids[/TD]
[TD]397 Surface Radar[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100 Asset Function Group[/TD]
[TD]118 Navaids[/TD]
[TD]436 VHF UHF Aerials[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100 Asset Function Group[/TD]
[TD]118 Navaids[/TD]
[TD]445 Windsock-Windsleeve[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100 Asset Function Group[/TD]
[TD]125 Runway Crossing[/TD]
[TD]000 N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100 Asset Function Group[/TD]
[TD]125 Runway Crossing[/TD]
[TD]073 Cable[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100 Asset Function Group[/TD]
[TD]125 Runway Crossing[/TD]
[TD]140 Control Panel[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100 Asset Function Group[/TD]
[TD]125 Runway Crossing[/TD]
[TD]150 Light Fitting Airfield Road[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100 Asset Function Group[/TD]
[TD]125 Runway Crossing[/TD]
[TD]321 Mast[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100 Asset Function Group[/TD]
[TD]125 Runway Crossing[/TD]
[TD]424 Traffic Lights[/TD]
[/TR]
</tbody>[/TABLE]

Many thanks in advance..

 

[AirportAndy71]

Just Bumping in case anyone missed this..