Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic Drop Down List
I have a workbook with 4 sheets.
Sheet 1 Column A allows users to enter a location name Sheet 2 Column B automatically creates a dynamic drop-down of the Sheet 1 Column A data How is this done? I can't find any code, functions, formulas or anything indicating how it is accomplished. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic Drop Down List
1. While on sheet 1, click InsertNameDefine 2. In the "Names in workbook" box, type in a name for your range (ex: "Locations") 3. In the "Refers To" box, paste in this formula: =OFFSET($A$1,0,0,COUNTA($A:$A),1) 4. Now on Sheet 2, highlight column B and goto DataValidation: Allow: List Source: =Locations Check the "In-cell dropdown" box if you like. 5. Click OK. HTH, JP On Jan 12, 1:08*pm, aeddave wrote: I have a workbook with 4 sheets. * Sheet 1 Column A allows users to enter a location name Sheet 2 Column B automatically creates a dynamic drop-down of the Sheet 1 Column A data How is this done? *I can't find any code, functions, formulas or anything indicating how it is accomplished. Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic Drop Down List
Thanks JP. This is is. However, although it appears to work dynamically, it
doesn't show the bottom two "locations" from Sheet1ColA in the drop-down. I'd appreciate any thoughts. "JP" wrote: 1. While on sheet 1, click InsertNameDefine 2. In the "Names in workbook" box, type in a name for your range (ex: "Locations") 3. In the "Refers To" box, paste in this formula: =OFFSET($A$1,0,0,COUNTA($A:$A),1) 4. Now on Sheet 2, highlight column B and goto DataValidation: Allow: List Source: =Locations Check the "In-cell dropdown" box if you like. 5. Click OK. HTH, JP On Jan 12, 1:08 pm, aeddave wrote: I have a workbook with 4 sheets. Sheet 1 Column A allows users to enter a location name Sheet 2 Column B automatically creates a dynamic drop-down of the Sheet 1 Column A data How is this done? I can't find any code, functions, formulas or anything indicating how it is accomplished. Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatic Drop Down List
Make sure there are no blank rows in Sheet 1, column A. The formula
assumes: number of entries = number of rows. HTH, JP On Jan 14, 10:29*am, aeddave wrote: Thanks JP. *This is is. *However, although it appears to work dynamically, it doesn't show the bottom two "locations" from Sheet1ColA in the drop-down. * I'd appreciate any thoughts. "JP" wrote: 1. While on sheet 1, click InsertNameDefine 2. In the "Names in workbook" box, type in a name for your range (ex: "Locations") 3. In the "Refers To" box, paste in this formula: =OFFSET($A$1,0,0,COUNTA($A:$A),1) 4. Now on Sheet 2, highlight column B and goto DataValidation: Allow: List Source: =Locations Check the "In-cell dropdown" box if you like. 5. Click OK. HTH, JP On Jan 12, 1:08 pm, aeddave wrote: I have a workbook with 4 sheets. * Sheet 1 Column A allows users to enter a location name Sheet 2 Column B automatically creates a dynamic drop-down of the Sheet 1 Column A data How is this done? *I can't find any code, functions, formulas or anything indicating how it is accomplished. Thanks in advance.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down lists that auto create and then filter the next drop down list | Excel Worksheet Functions | |||
Drop Down List choice selecting another drop down list | Excel Worksheet Functions | |||
how do I link a drop down list entry to a new drop down cell? | Excel Discussion (Misc queries) | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
automatic color change in cells using a drop down list | Excel Worksheet Functions |