Data validation based on adjacent cell

[matt-gilbert]

Hi. I need to create a dependant data validation list based on the adjacent cell value. I have done this before as per link below
http://www.contextures.com/exceldatavaldependindextablesindirect.html
This method however does not suit my requirement. My spreadsheet is attached and my requirement is as follows;
1. tbl_Cable_Schedule needs a drop down list in col B (Drum No). I would like the list to only show Drum No's that have the Cable Type as shown on adjacent cell in col A. Drum No comes from tbl_Drum_Schedule. For example, validation list on tbl_Cable_Schedule cell B6 should only list 1 and 6.
2. It would be nice for validation list to show tbl_Drum_Schedule col F records so that I don't have to keep switching back and forth to see how much cable is left on a particular drum. I have shown this under tbl_Cable_Schedule.
Any help would be greatly appreciated. Thanks!
Matt

 

[bosco_yip]

Try,

1] See attached file

2] In "Cable Types" sheet B2, copied across to J2 an all copied down :

=IFERROR(INDEX('Drum Schedule'!$A$1:$A$100,AGGREGATE(15,6,ROW('Drum Schedule'!$A$1:$A$100)/('Drum Schedule'!$B$1:$B$100=$A2),COLUMNS($A:A))),"")

3] In "Cable Schedule" sheet "Drum No" column, select C2 >> Data Validation >>

>> Allow : List

>> Sources :

=OFFSET('Cable Types'!$A$1,MATCH(A2,'Cable Types'!$A$2:$A$100,0),1,,COUNT(OFFSET('Cable Types'!$A$1,MATCH(A2,'Cable Types'!$A$2:$A$100,0),1,,100)))

>> And, copied down to C9

3] In "Cable Schedule" sheet "Drum Left(m)" column D2 , formula copied down :

=IF([@[Drum No]]="","",[@[Drum No]]&": LEFT: "&INDEX(tbl_Drum_Schedule[Left (m)],MATCH([@[Drum No]],tbl_Drum_Schedule[Drum No],0))&"m")

Regards
Bosco

 

[matt-gilbert]

Thanks Bosco. Works well except for item 3 in your post. Is there any way that this formula could be applied to "Cable Schedule" sheet "Drum No" column? This is the field that is used to select an appropriately sized drum and it would be great to see metres left on drum when making this selection. Otherwise, you would have to select drum and then view resultant data in "Drum Left (m)" column. The data provided in the sample sheet is only a small selection. There will be 200,000+ cable types on "Cable Schedule" and 10,000+ drum no's on "Drum Schedule" sheet...