ขอวิธีป้องกันความผิดพลาด ในการทำ Dependent Validation

[Singha]

อยากทราบวิธีป้องกันการเลือกข้อมูลที่ไม่อยู่ใน range ของ validation parent
ยกตัวอย่าง ผมสร้าง cell รับข้อมูลมา 2 cell คือ A1 และ B1
โดยให้ cell A1 สำหรับรับข้อมูลชื่อจังหวัดในประเทศไทย ซึ่งทำเป็น Validation แบบ list ไว้ และผูกกับ range "ชื่อจังหวัด"
และให้ cell B1 สำหรับรับข้อมูลชื่ออำเภอของจังหวัดที่เลือกก่อนหน้า โดยทำเป็น validation แบบ list และผูกกับ range "อำเภอ_จังหวัด" แล้ว ผูกด้วยสูตร =indirect("อำเภอ_"&B1)
ปัญหาคือ สมมุติผู้ใช้เลือกชื่อจังหวัดที่ cell A1 มา1จังหวัด เช่นเลือก กรุงเทพ จากนั้นก็เลือกช่อง B1 เป็น จตุจักร ก็ทำได้ตามปกติ แต่ถ้าหลังจากเลือกข้อมูลในช่อง B1 ไปแล้ว แล้ว ผู้ใช้เกิดเปลี่ยนใจกลับไปเลือกชื่อจังหวัดใหม่ เช่น เปลี่ยนกรุงเทพเป็นชลบุรี แล้วลืมเลือกชื่ออำเภอใหม่ด้วย ข้อมูลตอนนี้ก็จะเป็น จ.ชลบุรี อ.จตุจักร ซึ่งก็จะเกิดข้อผิดพลาดได้
ขอถาม 2 ข้อครับ
1.มีวิธีเขียนสูตรตรวจสอบว่าข้อมูลที่เลือกและส่งมา Coflict กัน
2.มีวิธีป้องกันการลืมเลือกชื่ออำเภอใหม่มั้ยครับ เช่นถ้าเลือกชื่ออำเภอแล้ว ถ้าต้องการเลือกจังหวัดใหม่ให้ Excel Clear ข้อมูลช่อง B1 ก่อน อะไรแบบนี้จะทำได้มั้ยครับ

[snasui]

การเขียนสูตรตรวจสอบสามารถทำได้แต่ต้องเขียนไว้ที่เซลล์อื่นเพื่อให้แสดงข้อความว่าเลือกข้อมูลที่สัมพันธ์กันหรือไม่ แต่ต้องแนบไฟล์มาด้วยจึงจะสามารถแนะนำต่อได้ครับ

Validation ไม่ใช่ Macro หรือ VBA ที่จะทำเช่นข้อ 2 ตามที่ถามมาได้ การจะทำเช่นนั้นต้องอาศัยการเขียนโปรแกรมด้วย VBA เข้าไปช่วย ซึ่งต้องเขียนมาเองก่อนตามกฎการใช้บอร์ดข้อ 5 ด้านบนครับ

[menem]

กรณีที่ต้องการตามข้อ 2 จำเป็นต้องใช้ VBA ครับ

แต่ถ้าอยากหลีกเลี่ยงการใช้ VBA อาจจะมีอีกวิธี ที่อาจจะนำไปประยุกต์ใช้ต่อได้
คือการ "ซ่อนการแสดงผล" ในกรณีที่ข้อมูลในเซล ไม่อยู่ใน List ของ Parent

** ข้อมูลใน Child Cell ยังผิดอยู่ เพียงแต่ถูกซ่อนไว้ไม่ให้เห็น
หากนำไปใช้ต่อเพื่อการคำนวณ ผลจะยังผิดอยู่นะครับ **

[DhitiBank]

หรืออีกวิธีหนึ่งคือ ป้องกันการเลือกจังหวัดใหม่ หากยังไม่ลบอำเภอเดิมทิ้งก่อนครับ สามารถทำได้โดย

1. สร้างฐานข้อมูลรายชื่อจังหวัด และอำเภอก่อน ผมเอามาจาก Thailand Provinces และผมสร้าง Name range เอาไว้เพื่อสะดวกเอามาใช้อ้างอิงครับ

2016-07-07 09_55_52-Microsoft Excel - Cascading dropdown list.xlsx.png

_ProvNo
=OFFSET(Database!$A$3,1,,MAX(1,COUNTA(Database!$A:$A)-2))

_AmpprovID
=OFFSET(Database!$H$3,1,,MAX(1,COUNTA(Database!$H:$H)-1))

2. อีกชีทหนึ่ง สร้าง cascading drop down list ที่เซลล์ A2 และ B2
~~~> เซลล์ A2 -> Data -> Data validation... -> Allow: List (เอาติ๊กถูกที่ Ignore blank ออก) -> Source: ให้คีย์
=IF(B2<>"","",OFFSET(_ProvNo,,1))

~~~> เซลล์ B2 เลือก Data validation เช่นเดียวกัน แต่ตรง Source ให้คีย์
=IF(A2="","",OFFSET(Database!F3,MATCH(INDEX(_ProvNo,MATCH(A2,OFFSET(_ProvNo,,1),0)),_AmpProvID,0),,COUNTIF(_AmpProvID,INDEX(_ProvNo,MATCH(A2,OFFSET(_ProvNo,,1),0)))))

- คราวนี้ หากต้องการเปลี่ยนจังหวัด ก็จะต้องลบอำเภอก่อน
- หากต้องการใส่อำเภอ ก็จำเป็นจะต้องมีชื่อจังหวัดในเซลล์ A2 ก่อน

ตามไฟล์แนบครับ