提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在下拉列表中隱藏以前使用的項目?

在Excel中,您可以快速創建一個正常的下拉列表,但是您是否曾嘗試在選擇一個項目時創建下拉列表,以前使用的項目將從列表中刪除? 例如,如果我有一個包含100名稱的下拉列表,當我選擇一個名稱時,我想從下拉列表中刪除此名稱,現在下拉列表中包含99名稱,等等,直到下拉列表為空。 也許,這對我們大多數人來說都是困難的,在這裡,我可以談談如何在Excel中創建這樣一個下拉列表。

在輔助列中將以前使用的項目隱藏在下拉列表中

Office選項卡在Office中啟用選項卡式編輯和瀏覽,使您的工作更輕鬆......
Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%
  • 重用任何東西: 將最常用或最複雜的公式,圖表和其他任何內容添加到您的收藏夾中,並在將來快速重複使用它們。
  • 超過20文本功能: 從文本字符串中提取數字; 提取或刪除部分文本; 將數字和貨幣轉換為英語單詞...
  • 合併工具:多個工作簿和表格合二為一; 合併多個單元格/行/列而不丟失數據; 合併重複行和總和...
  • 拆分工具:根據價值將數據拆分為多個表格; 一個工作簿到多個Excel,PDF或CSV文件; 一列到多列......
  • 粘貼跳過 隱藏/過濾行; 數和總和 按背景顏色; 創建郵件列表和 通過Cell的價值發送電子郵件...
  • 超級過濾器: 創建高級過濾方案並應用於任何工作表; 分類 按週,日,頻率等; 過濾 通過大膽,公式,評論......
  • 超過300強大的功能; 與Office 2007-2019和365一起使用; 支持所有語言; 在您的企業或組織中輕鬆部署。

箭頭藍色右泡 在輔助列中將以前使用的項目隱藏在下拉列表中


假設您在列A中具有如下圖所示的名稱列表,請按照以下步驟逐一完成此任務。

文檔隱藏使用的項,下拉列表,1

1。 除了你的名字,請輸入這個公式 = IF(COUNTIF($ F $ 1:$ F $ 11,A1)> = 1,“”,ROW()) 到單元格B1中,參見截圖:

文檔隱藏使用的項,下拉列表,1

注意:在上面的公式中, F1:F11是您想要放置下拉列表的單元格範圍,以及 A1 是你的名字單元格。

2。 然後將填充手柄拖到包含此公式的範圍,您將得到以下結果:

文檔隱藏使用的項,下拉列表,1

3。 繼續在列C中應用公式,請輸入以下公式: =IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$11),"",INDEX(A:A,SMALL(B$1:B$11,1+ROW(A1)-ROW(A$1)))) 到單元格C1中,看截圖:

文檔隱藏使用的項,下拉列表,1

4。 然後填寫這個公式到你需要的範圍,看截圖:

文檔隱藏使用的項,下拉列表,1

5。 現在您需要在列C中為這些名稱定義一個範圍名稱,選擇C1:C11(您在步驟4中應用公式的範圍),然後單擊 公式 > 定義名稱,看截圖:

文檔隱藏使用的項,下拉列表,1

6。 在 新名字 對話框中,在名稱文本框中鍵入一個名稱,然後輸入此公式 =OFFSET(Sheet2!$C$1,0,0,COUNTA(Sheet2!$C$1:$C$11)-COUNTBLANK(Sheet2!$C$1:$C$11),1) 字段,看截圖:

文檔隱藏使用的項,下拉列表,1

注意:在上面的公式中,C1:C11是您在步驟3中創建的幫助程序列範圍,並且工作表2是您正在使用的當前工作表。

7。 完成設置後,您可以創建一個下拉列表,選擇F1:F11單元格放置下拉列表,然後單擊 數據 > 數據驗證 > 數據驗證,看截圖:

文檔隱藏使用的項,下拉列表,1

8。 在 數據驗證 對話框,單擊 設置 標籤,然後選擇 名單 來自 下拉列表,然後在下 資源 部分,輸入這個公式: = namechecknamecheck 是您在步驟6中創建的範圍名稱),請參閱截圖:

文檔隱藏使用的項,下拉列表,1

9。 然後點擊 OK 按鈕關閉此對話框,現在,下拉列表已在選定範圍內創建,並且從下拉列表中選擇一個名稱後,此已用名稱將從列表中刪除,並且僅顯示尚未使用的名稱,看截圖:

文檔隱藏使用的項,下拉列表,1

小技巧: :您無法刪除您在上述步驟中製作的幫手列,如果您將其刪除,則下拉列表將無效。


相關文章:

如何在Excel中插入下拉列表?

如何在Excel中快速創建動態下拉列表?

如何在Excel中創建包含圖像的下拉列表?


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    John · 10 months ago
    Is there a way to have only some of the options get removed when selected and others be permanent?
  • To post as a guest, your comment is unpublished.
    Keith Race · 11 months ago
    How do I get this activity to work if I transpose from Row to Column
  • To post as a guest, your comment is unpublished.
    Sam · 1 years ago
    I’ve entered all the formulas correctly, but the only name showing up is the first one on the list. What am I doing wrong??
  • To post as a guest, your comment is unpublished.
    Stefan · 2 years ago
    Works great, however, if you have two people on the list with the same name e.g. John Smith it removes both incidents of 'John Smith' from the list when you select one of them.


    Is there a way to amend this so that you have have multiple versions of the one name without them all being removed?


    Thanks.
  • To post as a guest, your comment is unpublished.
    Derric · 3 years ago
    How would you change this formula for use of data validation list across multiple rows instead of a single column. Is that possible?

    Thanks
  • To post as a guest, your comment is unpublished.
    Mohanraj · 3 years ago
    Awesome, Working for me...
  • To post as a guest, your comment is unpublished.
    tim · 3 years ago
    I changed mine to accommodate my needs "=IF(COUNTIF(Statusboard!$C:$C,A1)>=1,"",ROW())" Make sure you change the $F$1:$F$11 from "=IF(COUNTIF($F$1:$F$11,A1)>=1,"",ROW())" to wherever your list is in my case it was Statusboard!$C:$C,A1.
  • To post as a guest, your comment is unpublished.
    Yolanda · 4 years ago
    I need to be able to create a list that has items that disappear but I want to be able to use it in multiple columns on the same sheet. Does anyone know how to do that? - Thanks!
  • To post as a guest, your comment is unpublished.
    Yolanda · 4 years ago
    I need to be able to use this list multiple times in multiple columns but as soon as an item disappears it's gone for good. Does anyone know how I can create this and be able to use it for multiple columns? Thanks!
  • To post as a guest, your comment is unpublished.
    NighT · 4 years ago
    Hey all,
    I got this to work, and it works like a charm!
    @Amanda, yes. I have my data on a different sheet as the dropdown menu. See the below written formulas.

    @Filip,
    Yes, you can use a formula to automatically select the unique values from a list. I used this to have a dynamic list.

    Mind you; I used google and a lot of different website to get to this formula, so it's not all my own work.
    First: to get the list of things to display:
    IF(INDEX(Sheet1!$A$2:$A$100;MATCH(0;COUNTIF($AA$14:AA14;Sheet1!$A$2:$A$100);0))=0;"";INDEX(Sheet1!$A$2:$A$100;MATCH(0;COUNTIF($AA$14:AA14;Sheet1!$A$2:$A$100);0)))

    === Basically this is the same formula twice. Which will give an empty ("") value if no further unique values are found. Anyway, the formula returns an unique list of values from my 'Sheet1!'. (lets say for easy reference I have this formula on Sheet 2, column A)

    Then I just start using the same formula as above (my sheet 2 column B):
    IF(COUNTIF(Sheet3!$S$2:$U$4;A1)>=1;"";ROW())

    === Sheet 3 is where I have my dropdowns. This is probably what you're looking for Amanda.

    Then the last bit of the formula:
    IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$24);"";INDEX(A:A;SMALL(B$1:B$24;1+ROW(A1)-ROW(A$1))))

    === This formula is in my Sheet 2 column C.
    This *should* work.

    Good luck to you all! And again, a great thank you to the author!
    Regards,

    NighT
  • To post as a guest, your comment is unpublished.
    Filip · 4 years ago
    Thanks for this guide. I'm just asking if it's possible when I update a value in formula, this value will be updated automatically in list of items?

    Example:
    I select value "James" from list in cell F
    Now, I want to change value from "James" to "Thomas". I rewrite value "James" to "Thomas" in cell A, formula automatically change value in cell C. It's OK, but I need this changed value is automatically updated in cell F as well.

    How can I reach that? Any ideas?
  • To post as a guest, your comment is unpublished.
    Amanda · 4 years ago
    Does anyone know if it is possible to use this between sheets? For instance if the original info (the column A portion) is on one sheet, but the dropdown (the column F portion) is on another? How would that change the formula?
  • To post as a guest, your comment is unpublished.
    Kent · 4 years ago
    Hi, I can't get it to work properly.
    When trying to complete step 8 I get a message about 'The source returns an error at evaluation. Do you wish to continue?'

    I am using Excel 2010, any idea?
    • To post as a guest, your comment is unpublished.
      Amanda · 4 years ago
      I had that happen at first, too. I had not changed the "sheet2" portion to the proper sheet name for what I was using.