[發問] 動態兩層下拉式選單

bridetobe

採購表單.rar (21.8 KB)

下載次數: 7

2014-8-8 10:33

想請問雙層動態下拉式選單的一些問題
利用品名清單的資料建立"廠商名"還有各個廠商的商品
然後在採購明細中
E欄使用動態的驗證
希望以後能在品名清單中持續增加廠商 (不管是增加在前面或後面都可以)
可是F欄卻無法使用
品名清單中廠商的商品也希望可以持續增加
F欄包含很多空白資料
找了很多資料了 但是好像都行不通

請問是範圍地方輸入錯誤嗎?還是有甚麼定義名稱的部份錯誤呢?

p212

回復 1# bridetobe
1、定義名稱「廠商名」之參照到建議修改成
=OFFSET(品名清單!$A$1,,,,COUNTA(品名清單!$1:$1))
2、另新定義名稱「Rng」，參照到輸入
=品名清單!$A$1:$Z$26
3、滑鼠點按「採購明細」工作表之儲存格F2，驗證資料來源輸入
=OFFSET(品名清單!$A$2,,MATCH($E2,廠商名,)-1,COUNTA(INDEX(Rng,0,MATCH($E2,廠商名,)))-1)
以儲存格F2為模板向下複製即可
4、名稱管理員中，除定義名稱「廠商名」與「Rng」外，其餘建議可刪除。
請參考！

bridetobe

回復 2# p212

謝謝
想請問第三點
F2 資料驗證輸入=OFFSET(品名清單!$A$2,,MATCH($E2,廠商名,)-1,COUNTA(INDEX(Rng,0,MATCH($E2,廠商名,)))-1)
EXCEL顯示 您不能在資料驗證的準則中參照到其他工作表或活頁簿

這樣該如何解決

p212

回復 3# bridetobe
Sorry！没注意到資料驗證跨工作表！
1、定義名稱「Start」，參照到輸入
=品名清單!$A$2
2、F2 資料驗證輸入=OFFSET(Start,,MATCH($E2,廠商名,)-1,COUNTA(INDEX(Rng,0,MATCH($E2,廠商名,)))-1)
請參考！

bridetobe

回復 4# p212


嗯...F欄部分沒辦法依照E欄的廠商對應出廠商有的產品
  EX E欄選豪品 應該要跑出下拉式選單只有乳膠手套528
      但是沒有 卻跑出飲用水和清機費

下載 (198.4 KB)

2014-8-8 14:54

真抱歉問題很多><

p212

回復 5# bridetobe
1、請複製原檔案(另起檔名)，清空F欄資料，再依照上述作法整理定義名稱與設定儲存格F2之資料驗證(其動態清單功能應該没問題)。
2、以採購明細工作表之儲存格F2(空白格)為模板，向下拖移複製套用清單格式。
3、若不想重新輸入F欄資料，則複製原檔案的F欄資料，以「貼上值」的方式填入資料即可。
請參考！

bridetobe

回復 6# p212


謝謝 上面的問題都解決了
但是就是我在新增廠商時
再B欄以及AB欄各新增一個廠商和一個產品

下載 (19.59 KB)

2014-8-8 15:41

在回到下拉式選單
123可以找出品名456
777卻找不出品名888

下載 (13.49 KB)

2014-8-8 15:39

p212

回復 7# bridetobe
請調整定義名稱「Rng」的「參照到」範圍
例如=品名清單!$A$1:$Z$26
調整為=品名清單!$A$1:$AB$26
請參考！

Hsieh

回復 7# bridetobe
把資料庫轉置作對照表的作法並不是好方法
再說那麼多定義名稱會累死人(每增廠商就要增加名稱)
將品項清單資料以A欄作排序
把第3個工作表捨棄，建立以下4個名稱
a=OFFSET(品項清單!$A$2,,,COUNTA(品項清單!$A:$A)-1,)
s=COUNTA(a)
x=OFFSET(品項清單!$B$1,MATCH(採購明細!$E2,品項清單!$A:$A,0)-1,,COUNTIF(品項清單!$A:$A,採購明細!$E2),)
廠商名=OFFSET(品項清單!$H$2,,,65536-COUNTBLANK(品項清單!$H:$H)-1,)
直接在品項清單工作表H2輸入陣列公式
=IF(ROW(採購明細!A1)>SUM(1/COUNTIF(a,a)),"",INDEX(a,SMALL(IF(COUNTIF(OFFSET(INDEX(a,1,1),,,ROW(INDIRECT("A1:A"&s)),),a)=1,ROW(INDIRECT("A1:A"&s)),""),ROW(採購明細!A1))))
下拉至出現空白
採購明細E欄驗證
=廠商名
採購明細F欄驗證
=x
    採購表單.zip (27.09 KB)

下載次數: 26

2014-8-8 16:11

bridetobe

回復 8# p212


  了解了!非常謝謝!