Using a dropdown to fill in multiple cells

T

Tyvent

New Member
Joined
Nov 12, 2017
Messages
6
Greetings,

I'm self-taught in Excel. Unfortunately, that may be why I'm not well-versed in Excel jargon, and that's probably why I'm having such a hard time looking up the information that I need.

Cutting to the chase, I need to create a dropdown list that, depending on the item selected, will fill in different values in multiple cells. I know how to create a basic dropdown list. I suspect the multiple value list should look something like this (the values listed are off the top of my head and probably inaccurate, but that's not important here) where the first column will have the dropdown items and the others will have the values to automatically input into other cells:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Food (1 serv)[/TD]
[TD]Carbs (g)[/TD]
[TD]Fat (g)[/TD]
[TD]Protein (g)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cheese[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Strawberries[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Almonds[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Vanilla[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Sugar[/TD]
[TD]30[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Yes, I have a cheesecake craving. Anyway, the sheet where I will select the dropdown and need the automatic input of the associated values looks something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Food 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Food 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Food[/TD]
[TD](Dropdown)[/TD]
[TD][/TD]
[TD]Food[/TD]
[TD](Dropdown)[/TD]
[/TR]
[TR]
[TD]Carbs (g)[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]Carbs (g)[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Fat (g)[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]Fat (g)[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Protein (g)[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]Protein (g)[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]

Again, I can create the basic dropdown. I need to find out what I need to do to automatically fill in the Carbs, Fat, and Protein values with, say, 4 10 and 10 respectively when I select Cheese from the dropdown.

Thank you for your time, folks.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
So you have a Datavalidation drop down list in column "A"

And if you choose Cheese and have Cheese entered into Range("A2") You want:
4 entered into Range("B2")
10 entered into Range("C2")
10 entered into Range ("D2")

Is this a example of what you want?
Are you willing to use Vba?
 
Last edited:
Upvote 0
Yes, that's exactly right.

And I'm willing to use vba, though I don't know what that is. If it'd be helpful for me to read up on it beforehand, I'm willing to do that, especially if you or someone can point me to the pertinent parts.
 
Upvote 0
Vba is Excel programming language where you can have Excel do all sorts of things.
Now some company's do not allow Excel users to use Vba.
We would need to know what version of Excel are you using
Are you using a Apple computer?

And how many values like Cheese will you be using?
Would it be like 10 or 100 or one thousand.

We could then write you a script and explain how to install and use it.

Now there could be someone on this forum who knows how to do something like this without Vba
Maybe with some formula.
I'm not good with large formulas
I'm more use to using Vba.

Answering these question would be a good start at helping you.
 
Upvote 0
These formulas should give you what you want...
Data
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][/tr]
[tr][td]
1​
[/td][td]Food (1 serv)[/td][td]Carbs (g)[/td][td]Fat (g)[/td][td]Protein (g)[/td][/tr]

[tr][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
3​
[/td][td]Cheese[/td][td]4[/td][td]10[/td][td]10[/td][/tr]

[tr][td]
4​
[/td][td]Strawberries[/td][td]20[/td][td]0[/td][td]1[/td][/tr]

[tr][td]
5​
[/td][td]Almonds[/td][td]4[/td][td]10[/td][td]6[/td][/tr]

[tr][td]
6​
[/td][td]Vanilla[/td][td]0[/td][td]0[/td][td]0[/td][/tr]

[tr][td]
7​
[/td][td]Sugar[/td][td]30[/td][td]0[/td][td]0[/td][/tr]
[/table]

Output
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr]
[tr][td]
12​
[/td][td]Food 1[/td][td][/td][td][/td][td]Food 2[/td][td][/td][/tr]

[tr][td]
13​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
14​
[/td][td]Food[/td][td]Cheese[/td][td][/td][td]Food[/td][td]Sugar[/td][/tr]

[tr][td]
15​
[/td][td]Carbs (g)[/td][td]4[/td][td][/td][td]Carbs (g)[/td][td]30[/td][/tr]

[tr][td]
16​
[/td][td]Fat (g)[/td][td]10[/td][td][/td][td]Fat (g)[/td][td]0[/td][/tr]

[tr][td]
17​
[/td][td]Protein (g)[/td][td]10[/td][td][/td][td]Protein (g)[/td][td]0[/td][/tr]
[/table]
B15=INDEX($B$3:$D$7,MATCH(B$14,$A$3:$A$7,0),MATCH(A15,$B$1:$D$1,0))
copy that down, and copy down to the next area, too
 
Upvote 0
Answering the questions of My Answer Is This:

Using a PC, Excel 2013, about 10 values.

FDibbins said:
These formulas should give you what you want...
Data
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]Food (1 serv)[/TD]
[TD]Carbs (g)[/TD]
[TD]Fat (g)[/TD]
[TD]Protein (g)[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Cheese[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]Strawberries[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]Almonds[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]Vanilla[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]Sugar[/TD]
[TD]30
[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Output
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]Food 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Food 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD]Food[/TD]
[TD]Cheese[/TD]
[TD][/TD]
[TD]Food[/TD]
[TD]Sugar[/TD]
[/TR]
[TR]
[TD]
15​
[/TD]
[TD]Carbs (g)[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]Carbs (g)[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]
16​
[/TD]
[TD]Fat (g)[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]Fat (g)[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]
17​
[/TD]
[TD]Protein (g)[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]Protein (g)[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

B15=INDEX($B$3:$D$7,MATCH(B$14,$A$3:$A$7,0),MATCH(A15,$B$1:$D$1,0))
copy that down, and copy down to the next area, too
Click to expand...

A clarification: the dropdown table will be on a different sheet, specifically named 'Drop-down lists' without the apostrophes. The dropdown cells will be on a sheet called 'Selections' w/o apostrophes.

With that in mind, redoing the above formula pretending the first bunch of cells is on Drop-down lists and the second bunch is on Selections, would it become the following? =INDEX('Drop-down lists'!$B$3:$D$7,MATCH(B$14,'Drop-down lists'!$A$3:$A$7,0),MATCH(A15,'Drop-down lists'!$B$1:$D$1,0))
 
Upvote 0
If you want to use Vba try this:

I did three for you. As you enter a certain value in column "A" the other values go into columns B C and D

The best way to use Vba is to see how things work and learn how to do things yourself.

So you should be able to see how this works and add more case statements and enter the other values.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Notice the items marked in Red and change as needed for your specific needs

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row > 1 And Target.Value <> "" Then
MyVal = Target.Value
    With Target.Value
        Select Case MyVal
            Case "[COLOR=#ff0000]Cheese[/COLOR]"
                Target.Offset(0, 1).Value = [COLOR=#ff0000]4[/COLOR]: Target.Offset(0, 2).Value = [COLOR=#ff0000]10[/COLOR]: Target.Offset(0, 3).Value =[COLOR=#ff0000] 5[/COLOR]
            Case "Strawberries"
                Target.Offset(0, 1).Value = 20: Target.Offset(0, 2).Value = 0: Target.Offset(0, 3).Value = 1
            Case "Almonds"
                Target.Offset(0, 1).Value = 4: Target.Offset(0, 2).Value = 10: Target.Offset(0, 3).Value = 6
            
        End Select
    End With
End If
End Sub
 
Last edited:
Upvote 0
FDibbins said:
OK so does that work for you?
Click to expand...

Okay, so I tried adapting the formula to the sheets once I'd entered all the table data and came up with a circular reference error. Thought that might be due to a typo, but I didn't notice one.

Therefore, to make sure it wasn't a formula problem, I recreated the exact example we used above. That worked perfectly as you predicted.

So then I moved the data table to the 'Drop-down list' sheet (starting the table at A1 so that the only change would be adding the 'Drop-down list'! references. It won't let me hit Enter on the formula: "We found a problem with this formula. Try clicking Insert Function (yadda yadda) . . ." The error notice then tells me that if I want to write out =1+1 without Excel thinking I'm trying a formula to add an ' first. Upon clicking OK, the last 'Drop-down list' has 'Drop highlighted.

Typing this out, it just occurred to me that I should try renaming Drop-down list to DDList to eliminate the space and the need to ' ' the sheet name in the formula. I'll give that a shot after dinner and let you guys know what happens if you need that info to help me further.

Worst case scenario, I can just throw the data table on the same sheet and hide the cells I guess.
 
Upvote 0
My Aswer Is This said:
If you want to use Vba try this:

I did three for you. As you enter a certain value in column "A" the other values go into columns B C and D

The best way to use Vba is to see how things work and learn how to do things yourself.

So you should be able to see how this works and add more case statements and enter the other values.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Notice the items marked in Red and change as needed for your specific needs

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row > 1 And Target.Value <> "" Then
MyVal = Target.Value
    With Target.Value
        Select Case MyVal
            Case "[COLOR=#ff0000]Cheese[/COLOR]"
                Target.Offset(0, 1).Value = [COLOR=#ff0000]4[/COLOR]: Target.Offset(0, 2).Value = [COLOR=#ff0000]10[/COLOR]: Target.Offset(0, 3).Value =[COLOR=#ff0000] 5[/COLOR]
            Case "Strawberries"
                Target.Offset(0, 1).Value = 20: Target.Offset(0, 2).Value = 0: Target.Offset(0, 3).Value = 1
            Case "Almonds"
                Target.Offset(0, 1).Value = 4: Target.Offset(0, 2).Value = 10: Target.Offset(0, 3).Value = 6
            
        End Select
    End With
End If
End Sub
Click to expand...

Thanks! I'll give this a whirl in a bit and let you know what I find.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Creating macro for nested calculations within calculations
Replies
2
Views
116
LizN
L
N
  • Question Question
Bring nutrition values across from food selected from dropdown list
Replies
1
Views
248
NateSC
NateSC
A
  • Question Question
Show Data Validation Lists in Next Row of Table
Replies
0
Views
942
AndyPandy27
A
S
  • Question Question
Sorting through Data with Multiple Criteria (Index, Match, CountIF)?
Replies
1
Views
277
STATluver
S
P
  • Question Question
Dropdown list not working
Replies
0
Views
299
pyclen
P

Forum statistics

Threads
1,223,721
Messages
6,174,096
Members
452,542
Latest member
Bricklin

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