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

帖子: 618
主題: 17
精華: 0
積分: 663
點名: 1
作業系統: WINDOWS 7
軟體版本: OFFICE 2013
閱讀權限: 50
性別: 男
來自: Pingtung
註冊時間: 2011-9-2
最後登錄: 2024-7-5

2014馬年全勤獎章 2013蛇年全勤獎章熱烈參與勳章

2^#

發表於 2014-8-8 11:38 | 只看該作者

本帖最後由 p212 於 2014-8-8 11:47 編輯

回復 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」外，其餘建議可刪除。
請參考！

帖子: 28
主題: 6
精華: 0
積分: 50
點名: 0
作業系統: Windows7
軟體版本: Office2003
閱讀權限: 20
性別: 女
來自: 新北市
註冊時間: 2014-7-2
最後登錄: 2014-8-25

3^#

發表於 2014-8-8 12:01 | 只看該作者

回復 2# p212

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

這樣該如何解決

p212

帖子: 618
主題: 17
精華: 0
積分: 663
點名: 1
作業系統: WINDOWS 7
軟體版本: OFFICE 2013
閱讀權限: 50
性別: 男
來自: Pingtung
註冊時間: 2011-9-2
最後登錄: 2024-7-5

2014馬年全勤獎章 2013蛇年全勤獎章熱烈參與勳章

4^#

發表於 2014-8-8 12:40 | 只看該作者

本帖最後由 p212 於 2014-8-8 12:43 編輯

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

帖子: 28
主題: 6
精華: 0
積分: 50
點名: 0
作業系統: Windows7
軟體版本: Office2003
閱讀權限: 20
性別: 女
來自: 新北市
註冊時間: 2014-7-2
最後登錄: 2014-8-25

5^#

發表於 2014-8-8 14:55 | 只看該作者

回復 4# p212

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

真抱歉問題很多><

p212

帖子: 618
主題: 17
精華: 0
積分: 663
點名: 1
作業系統: WINDOWS 7
軟體版本: OFFICE 2013
閱讀權限: 50
性別: 男
來自: Pingtung
註冊時間: 2011-9-2
最後登錄: 2024-7-5

2014馬年全勤獎章 2013蛇年全勤獎章熱烈參與勳章

6^#

發表於 2014-8-8 15:19 | 只看該作者

本帖最後由 p212 於 2014-8-8 15:23 編輯

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

帖子: 28
主題: 6
精華: 0
積分: 50
點名: 0
作業系統: Windows7
軟體版本: Office2003
閱讀權限: 20
性別: 女
來自: 新北市
註冊時間: 2014-7-2
最後登錄: 2014-8-25

7^#

發表於 2014-8-8 15:40 | 只看該作者

本帖最後由 bridetobe 於 2014-8-8 15:41 編輯

回復 6# p212

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

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

p212

帖子: 618
主題: 17
精華: 0
積分: 663
點名: 1
作業系統: WINDOWS 7
軟體版本: OFFICE 2013
閱讀權限: 50
性別: 男
來自: Pingtung
註冊時間: 2011-9-2
最後登錄: 2024-7-5

2014馬年全勤獎章 2013蛇年全勤獎章熱烈參與勳章

8^#

發表於 2014-8-8 16:08 | 只看該作者

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

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 280
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-12-27

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

9^#

發表於 2014-8-8 16:11 | 只看該作者

本帖最後由 Hsieh 於 2014-8-8 16:12 編輯

回復 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)

學海無涯_不恥下問

帖子: 28
主題: 6
精華: 0
積分: 50
點名: 0
作業系統: Windows7
軟體版本: Office2003
閱讀權限: 20
性別: 女
來自: 新北市
註冊時間: 2014-7-2
最後登錄: 2014-8-25

10^#

發表於 2014-8-15 21:12 | 只看該作者

回復 8# p212

了解了!非常謝謝!