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.

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.

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.

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 -