How would you do this?

[smittycft99]

I have the following column:

Flat db bench
Flat barbell bench
Flat db flyes
Incline db bench
Incline barbell bench
Incline db bench
ect....(includes comprehensive exercise list)

I set up a spreadsheet to log workouts
Let's say in A1, user will start typing an exercise, ie. Incline, I would like all of the Incline exercises to come up in a drop down menu, so they can click on it to populate the cell..

Any input is greatly appreciated , thank you

[zacemmel]

Try using data validation - pick from list.

[Joe Was]

you want to dropdown list a set of items and be able to add your own item, then:

To the right of your sheet build a list one item per row in one column. Click the column ID to highlight the column, then use:

Insert-Name-Define then name your list.

Click the cell you want the list in, then:

Data-Validation-Settings (select "List" from Allow) in "Source" add =your list name, Like: =myList

Then copy the cell you just put the dropdown list in and highlight the other cells you want the list in and hit special paste-validation.

Now when a cell with your dropdown is selected, it grows a dropdown arrow, which when clicked returns your selection list.

To limit the selection to only the values in your list:

Data-validation-Settings
Allow: List
Check: Ignore blank
Check: In-Cell dropdown
Source: =$AA:$AA (this is the column "myList" is in.

Then:

Tab to Error Alert
Check: Show
Style: Stop
Title: Error!
Error Message: Only select from the dropdown list!

Now the user can put a wrong entery in the cell but they cannot move from the cell without an Error Box Message and the options to fix or erase their entery or start over.

Hope this helps. JSW

[smittycft99]

I looked at the data validation option, but I am a newbie and am not sure how to use it.

[Brian from Maui]

Smitty

In addition to Joe's post read this thread.

http://www.mrexcel.com/board/viewtop...c=7345&forum=2

[smittycft99]

Thanks Joe Was, works great!

What if in the drop down box, I had categories, IE. When someone clicks on the drop down box, at first they only see
CHEST
BACK
SHOULDERS
LEGS
ARMS
Then they would click on the category and THEN they would see all the exercises on each one.

How could I do that?

[smittycft99]

Thanks Brian from Maui, great link

[Joe Was]

The first code is manual the second is automatic.

Sub BuildList()
'By Joe Was
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
Else
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
End If
End Sub

To use the code!
Select an item in the dropdown in B1 (Like the: "State")
H column contains the list names for the lists in columns I, J & K, H would be the State list range name for the City list in column I, J & K or more column?
The list selected in B1 will set the dropdown values of D1 (Like the "Cities" in your selected State")

So you have one dropdown in B1 and the other in D1. When you select from the dropdown in B1 your selection loads the range name for the list used for the dropdown in D1. My test is B1 is three States and D1 is the Cities in that selected State. Hope this helps? JSW

P.S. The Private Sub automatically checks for a change in B1,s value and runs the macro to update D1.

[smittycft99]

Thanks Joe works great!