Indirect Data Validation

Hi Excel Experts,

I need some help on data validation. I have in tab Checklist which the data is coming from Sheet3 Column A but I cannot use data validation as it has a lot of duplicate records.
Bigger problem is for example if I select "DBMW" in tab Checklist Column C4 I only want to see the countries in Column D4 which are listed in tab Sheet3 which column Service Tower = "DBMW" and so on for tab Checklist column E4 I want to only see the locations available if Sheet3 Column Service Tower="DBMW" & Sheet3 Column LBU="CA".

I tried to use the Indirect formula but I am not getting the correct result. Anyone there able to help me with this? Attached is the Excel File sample and how I want it to look like.

Kind Regards,
Mark.

Originally Posted by shukla.ankur281190

Ok try in checklist

I4=

Formula:

Please Login or Register  to view this content.

J4

Formula:

Please Login or Register  to view this content.

Copy down.

Create name manger LBU =OFFSET(CheckList!$I$4,,,COUNTIF(CheckList!$I:$I,"?*"))

Create another manger Location =OFFSET(CheckList!$J$4,,,COUNTIF(CheckList!$J:$J,"?*"))

on D4 cell create data validation in source press f3 and select LBU same thing for Location.

Check the attached file.

hope this should help.

Hi Shukla,

This works perfectly well for Column C & D but Column E contains still extra data?
For example if I select DBMW & BR, I should only get to see 1 record in the list but yours shows more than 1 records. For most of it it has more data in Column E but column D works well.

Kind Regards,
MArk.

Originally Posted by shukla.ankur281190

Drag formula of J column until 100 row perhaps.

Hi Shukla,

I tried that but there is still a lot of data when DBMW & BR should only show one data in Column E. :-(

Kind Regards,
Mark.

Originally Posted by shukla.ankur281190

Oh sorry my mistake.

Try


J4

Formula:

Please Login or Register  to view this content.

Drag down!

Hi Shukla,

This works perfectly. You are a genius sir.
Manyt hanks for your help. :-)

Kind Regards,
MArk.

i think this is better:
=IFERROR(INDEX(l!$E$2:$E$2204,SMALL(INDEX(((l!$B$2:$B$2204<>$D$4)+(l!$A$2:$A$2204<>$C$4)+COUNTIF($J$3:J3,l!$E$2:$E$2204))*10^10+ROW(l!$A$2:$A$2204)-ROW(l!$A$1),),1)),"")

Originally Posted by tim201110

i think this is better:
=IFERROR(INDEX(l!$E$2:$E$2204,SMALL(INDEX(((l!$B$2:$B$2204<>$D$4)+(l!$A$2:$A$2204<>$C$4)+COUNTIF($J$3:J3,l!$E$2:$E$2204))*10^10+ROW(l!$A$2:$A$2204)-ROW(l!$A$1),),1)),"")

Hi Tim,

This works perfectly as well.!
Thank you very much. :-)

Kind Regards,
MArk.