Using a dropdown to fill in multiple cells

[Tyvent]

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:

Food (1 serv)	Carbs (g)	Fat (g)	Protein (g)
Cheese	4	10	10
Strawberries	20	0	1
Almonds	4	10	6
Vanilla	0	0	0
Sugar	30	0	0

<tbody>
</tbody>

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:

Food 1			Food 2
Food	(Dropdown)		Food	(Dropdown)
Carbs (g)	x		Carbs (g)	x
Fat (g)	x		Fat (g)	x
Protein (g)	x		Protein (g)	x

<tbody>
</tbody>

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.

 

[My Aswer Is This]

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?

 

[Tyvent]

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.

 

[My Aswer Is This]

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.

 

[FDibbins]

These formulas should give you what you want...
Data

	A​	B​	C​	D​
1​	Food (1 serv)	Carbs (g)	Fat (g)	Protein (g)
2​
3​	Cheese	4	10	10
4​	Strawberries	20	0	1
5​	Almonds	4	10	6
6​	Vanilla	0	0	0
7​	Sugar	30	0	0

Output

	A​	B​	C​	D​	E​
12​	Food 1			Food 2
13​
14​	Food	Cheese		Food	Sugar
15​	Carbs (g)	4		Carbs (g)	30
16​	Fat (g)	10		Fat (g)	0
17​	Protein (g)	10		Protein (g)	0

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

 

[Tyvent]

Answering the questions of My Answer Is This:

Using a PC, Excel 2013, about 10 values.

These formulas should give you what you want...
Data

	A​	B​	C​	D​
1​	Food (1 serv)	Carbs (g)	Fat (g)	Protein (g)
2​
3​	Cheese	4	10	10
4​	Strawberries	20	0	1
5​	Almonds	4	10	6
6​	Vanilla	0	0	0
7​	Sugar	30	0	0

<tbody>
</tbody>

Output

	A​	B​	C​	D​	E​
12​	Food 1			Food 2
13​
14​	Food	Cheese		Food	Sugar
15​	Carbs (g)	4		Carbs (g)	30
16​	Fat (g)	10		Fat (g)	0
17​	Protein (g)	10		Protein (g)	0

<tbody>
</tbody>

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

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))

 

[FDibbins]

OK so does that work for you?

 

[My Aswer Is This]

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

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

 

[Tyvent]

OK so does that work for you?

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.

 

[Tyvent]

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

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

Thanks! I'll give this a whirl in a bit and let you know what I find.