Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing drop down option after its been selected
I have a drop down menu with a list of 100 items. I have it set up that at
the end of each day. a coworker can go into the spreadsheet and pull from the drop down menu and select if they have used any of the 100 items. Is there any way to make it so that once a particular item has been selected from the drop down menu, that it no longer shows up as an option? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing drop down option after its been selected
Believe Debra covers what you seek he
http://www.contextures.com/xlDataVal03.html Excel -- Data Validation -- Hide Previously Used Items in Dropdown -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "bbennett2" wrote: I have a drop down menu with a list of 100 items. I have it set up that at the end of each day. a coworker can go into the spreadsheet and pull from the drop down menu and select if they have used any of the 100 items. Is there any way to make it so that once a particular item has been selected from the drop down menu, that it no longer shows up as an option? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing drop down option after its been selected
Place this macro in the sheet module of the sheet you are using. I assumed
the Data Validation cell is C1 and the list is in Column A starting with A1. The list must be named "TheList". If your list is located in some other sheet, preface the line that starts with Range("TheList").Find with Sheets("SheetName"). like this: Sheets("SheetName").Range("TheList").Find..... HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("C1")) Is Nothing Then Range("TheList").Find(What:=Target.Value, LookAt:=xlWhole). _ Delete Shift:=xlUp Range("A1", Range("A" & Rows.Count).End(xlUp)).Name = "TheList" End If End Sub "bbennett2" wrote in message ... I have a drop down menu with a list of 100 items. I have it set up that at the end of each day. a coworker can go into the spreadsheet and pull from the drop down menu and select if they have used any of the 100 items. Is there any way to make it so that once a particular item has been selected from the drop down menu, that it no longer shows up as an option? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing drop down option after its been selected
Thank you very much for your help...that is exactly what i was looking for.
I have one other question if you could help me out. this line is in the tutorial you sent me: =IF(COUNTIF(Schedule!$B$2:$B$7,A1)=1,"",ROW()) I understand what the code is doing...what i need is for this code to do not just the b collumn, but also the c, d, and e, collumns. Is there anyway to add that code into this line? "Max" wrote: Believe Debra covers what you seek he http://www.contextures.com/xlDataVal03.html Excel -- Data Validation -- Hide Previously Used Items in Dropdown -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "bbennett2" wrote: I have a drop down menu with a list of 100 items. I have it set up that at the end of each day. a coworker can go into the spreadsheet and pull from the drop down menu and select if they have used any of the 100 items. Is there any way to make it so that once a particular item has been selected from the drop down menu, that it no longer shows up as an option? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing drop down option after its been selected
Thank you very much for your help...that is exactly what i was looking for.
Good to hear. Please take a moment to press the YES button (like the one below) in that response I have one other question .. Suggest you take this up separately in a new thread -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Mail to: hyperlink option | Excel Discussion (Misc queries) | |||
Cells Not Calculating - Calc Option Selected | Excel Discussion (Misc queries) | |||
Forms Option Buttons(Can be right mouse selected when sheet is loc | Excel Discussion (Misc queries) | |||
Format drop down option won't open on selected cells | Excel Discussion (Misc queries) | |||
Option Button - multiple selected | Excel Worksheet Functions |