Drop down list selection changes cells

J

joevda

New Member
Joined
Jul 30, 2017
Messages
5
Hello,

I'm trying to make a spreadsheet for the family reunion that can be shared and answered by the recipients.
I have a list of names, and a box where they can select which food they want. What I wanted is when I click a name(that has already been filled in by that user, that the box with the food selection will change to whatever they selected, and then if I select another name, then it well show what they ordered instead. I'm not sure if this is possible (although I would imagine it would be)

Thanks in advance,
Joseph
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
I always think it's best to give specific details.
You said:
I have a list of names, and a box

You did not say where this list of names are
And you said I have a box

What is a Box?

Excel has a Activex and Form Control TextBox

Excel has a ListBox and a ComboBox.

Please be specific and maybe I could help you.

Excel does not have something named a Box


​
 
Upvote 0
So basically, there is a data validation list, with the names of all the people, and what I meant by "box" is basically a table (Just a bunch of cells with borders around them) where the user can type the amount of a certain meal they want.

And what I wanted is for example, if Steve clicks on his name in the data validation list, and he fills in what he wants to order, then it will save those values to his name, so whenever I open the sheet and select Steve's name, it will show what he wants.

Sorry not being specific enough.

Thanks,
Joseph
 
Upvote 0
What cell contains the drop down list?

Re the "box" - more info required than "Just a bunch of cells" :
What cells make up the box?
Any heading?
What data is in the box and where is it?
What is entered to make a selection and where?
 
Upvote 0
Joseph,
Welcome to the Forum.
Below is an example of a setup where the drop down for the 'Name' is in C3 and the List of Names is in column L.
I used Forum Tools 'Copy Range' to Generate the BB Code to show my setup.
The drop down for the 'Meals Chosen' is in F3 and the List of Meals Available is in column P.
After the Name and the Meal have been selected, the Meal Chosen is copied to column M on the same row as the Name in Column L.

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][th]
K​
[/th][th]
L​
[/th][th]
M​
[/th][th]
N​
[/th][th]
O​
[/th][th]
P​
[/th][th]
Q​
[/th][th]
R​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td][/td][td][/td][td]Name:[/td][td][/td][td][/td][td]Meal Chosen:[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td][/td][td][/td][td]Pete[/td][td][/td][td][/td][td]Kielbasa and Kraut[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Names[/td][td]Meal Selected[/td][td][/td][td][/td][td]Meals Available[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Pete[/td][td]Kielbasa and Kraut[/td][td][/td][td][/td][td]Hamburger n Fries[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Mike[/td][td][/td][td][/td][td][/td][td]Pork n Beans[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Joe[/td][td][/td][td][/td][td][/td][td]Kielbasa and Kraut[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Pat[/td][td]Hamburger n Fries[/td][td][/td][td][/td][td]Enchiladas and Salad[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Henry[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Marsha[/td][td]Pork n Beans[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Lowen[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]




Put the macro in a standard module and save the file as macro enabled, then
After you select the Name and Meal Chosen, run the following macro to place the Meal Chosen in Column M in the same row as the Name in column L.
I included code to catch if either the Name or Meal Chosen are omitted. Try it and you'll see how it works fairly quickly.
I used a Form Control Button to run the macro. That can be changed if required to run if either the Name or Meal Chosen are changed (using a Worksheet_Change Event format).
Code: 
Sub NamesMeals()
Dim x As Range
Dim LookupRange As Range
Dim LastLRow As Long


Select a Name and Meal Chosen from  drop down lists then run the macro
LastLRow = Range("L" & Rows.Count).End(xlUp).Row


If Cells(3, "C") = "" Then
    MsgBox "No Name Entered!"
    Exit Sub
End If


   Set LookupRange = Range("L5:L" & LastLRow)


    For Each x In LookupRange
        If InStr(1, Cells(3, "C"), x.Value, vbTextCompare) > 0 Then
            If Cells(x.Row, "M") = "" And Cells(3, "F") = "" Then
                MsgBox "No Meal selected"
                Exit Sub
            End If
            
            If Cells(x.Row, "M") = "" And Cells(3, "F") <> "" Then
                Cells(x.Row, "M") = Cells(3, "F")
                Exit Sub
            End If
            
            If Cells(x.Row, "M") <> "" And Cells(3, "F") <> "" And Cells(x.Row, "M") <> Cells(3, "F") Then
                Cells(x.Row, "M") = Cells(3, "F")
                Exit Sub
            End If
            
            If Cells(x.Row, "M") <> "" And Cells(3, "F") = "" Then
                Cells(3, "F") = Cells(x.Row, "M")
                Exit Sub
            End If


        End If
    Next x
End Sub


Lastly, I noticed in post #3 that you mentioned a "...the amount of a certain meal they want". I have not included that, but it could be easily added, maybe in column I and copied to column O in my example setup. The location of the dropdowns, List of Names, List of Meals Available, and Quantity might be different in your setup, so I haven't added the Quantity in this post.
Let me know if the above is helpful.
Perpa
 
Last edited:
Upvote 0
https://ibb.co/F4RLSHD

that link is to a picture of basically what I was trying to achieve. In the table with beside the meals, they select what they want, and when I change the name at the top, I want it to change the table to show what the other person has ordered.:)
 
Upvote 0
Assumptions :
• The drop down is in H3
• The meals are in column A with the first meal (Steak) in A8
• The 3 columns for quantities are D:F

Set-up :
• Add a new sheet and name it Sheet2 (the sheet can be hidden if desired)
• Put a button on the original sheet for the purpose of confirming quantities entered
• Assign this macro to the button :
Code: 
Sub ConfirmSelection()
Dim rws%, n As Range
rws = Cells(Rows.Count, "A").End(xlUp).Row - 7
With Sheets("Sheet2")
    Set n = .Rows(1).Find([H3])
    If n Is Nothing Then
        Set n = .Cells(1, Columns.Count).End(xlToLeft)(1, 2)
        n.Resize(, 3) = [H3]
    End If
    .Cells(2, n.Column).Resize(rws, 3) = [D8].Resize(rws, 3).Value
End With
End Sub
• Put this in the original sheet module :
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sRng As Range, n As Range
If Target.Address = "$H$3" Then
Application.EnableEvents = False
Set sRng = Range("D8:F" & Cells(Rows.Count, "A").End(xlUp).Row)
With Sheets("Sheet2")
    Set n = .Rows(1).Find([H3], LookAt:=xlWhole)
    If n Is Nothing Then
        sRng.ClearContents
    Else
        sRng = .Cells(2, n.Column).Resize(sRng.Rows.Count, 3).Value
    End If
End With
Application.EnableEvents = True
End If
End Sub

Notes :
• After entering quantities for the name per H3, click the button to confirm and save
• Meals can be added to the bottom of the meals list and the code automatically handles it.
• If meals are inserted or deleted, or if the sequence is changed, the quantities for all names will have to be re-input to account for the changes
• The spaghetti Bolognaise seems a bit expensive
 
Last edited:
Upvote 0
joevda said:
Hello,

thanks for you help so far on this https://www.mrexcel.com/forum/excel...ction-changes-cells.html?posted=1#post5236229.

However I have 1 more question.

The macro you posted writes the name 3 times, https://ibb.co/K5TV1gT

If possible I'd want it to look like this, https://ibb.co/QfVwyJ2

And also the price for the spaghetti is not right lol, I just typed random numbers as an example.


Thanks again,
Joseph
Click to expand...
Since Sheet2 is just a working sheet which stores the saved selections, the format is not really important.
But to format per your post :
Code: 
Sub ConfirmSelection()
Dim rws%, n As Range
rws = Cells(Rows.Count, "A").End(xlUp).Row - 7
With Sheets("Sheet2")
    Set n = .Rows(1).Find([H3])
    If n Is Nothing Then
        Set n = .Cells(1, Columns.Count).End(xlToLeft)(1, 2)
        n = [H3]
        n.Resize(, 3).HorizontalAlignment = xlCenterAcrossSelection
        n(2) = "Adult"
        n(2, 2) = "Child"
        n(2, 3) = "-3"
    End If
    .Cells(3, n.Column).Resize(rws, 3) = [D8].Resize(rws, 3).Value
End With
End Sub
 
Upvote 0
Code: 
Sub ConfirmSelection()
Dim rws%, n As Range
rws = Cells(Rows.Count, "A").End(xlUp).Row - 7
With Sheets("Sheet2")
    Set n = .Rows(1).Find([H3])
    If n Is Nothing Then
        If .Cells(2, Columns.Count).End(xlToLeft).Column = 1 Then
            Set n = .[B1]
        Else: Set n = .Cells(2, Columns.Count).End(xlToLeft)(0, 2)
        End If
        n = [H3]
        n.Resize(, 3).HorizontalAlignment = xlCenterAcrossSelection
        n(2) = "Adult"
        n(2, 2) = "Child"
        n(2, 3) = "-3"
    End If
    .Cells(3, n.Column).Resize(rws, 3) = [D8].Resize(rws, 3).Value
End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Bring nutrition values across from food selected from dropdown list
Replies
1
Views
248
NateSC
NateSC
S
  • Question Question
Excel Drop Down Multiple Select/Remove & Auto Alphabetize Selections
Replies
0
Views
241
Sactown Steve
S
R
  • Question Question
Smarter alternative to Name Manager & Indirect Formula?
Replies
1
Views
380
RoryA
RoryA
P
  • Question Question
How to create a multiple drop-down lists in Excel using Developer?
3 4 5
Replies
41
Views
704
PetarTen
P
E
  • Question Question
Mirror selections from multi-select drop down list in another cell
Replies
4
Views
290
Eccymarge
E

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top