Solved How can I use a drop down list to populate other fields?

Microsoft Outlook 2013 32/64-bit (mail m...
December 31, 2018 at 11:06:01
Specs: Windows 64
On one spreadsheet I have a list of food with the calorie, carb, and protein beside it like this:

         A                                   B                      C             D                    E
1      Item	               Calories	             Carbs	   Protein	        Quantity
2      Danimals	                   50	              11	         2	            1
3     Tuna Pack, lemon             80	               1	        18	           1
4     Coffee, med, 3 cream        210	               3	         3	            1

On another spreadsheet I have the same column names but with a Date and Time column first.

               A                        B          C                             D                          E                    F
1              Date	              Time       Item	                          Calories	        Carbs	           Protein
2             12/31/2018		         Danimals			
3		                                Coffee, med, 3 cream	

Column C is a drop down list and I'd like to have the calories, carbs, and protein amount come over at the same time and they have to be in separate cells so I can get a carb and protein count at the end of day. I'm also creating my own rather than using something online because I want to control the food options, like specializing my own recipes.

Yes, my New Year's Resolution is to eat better! LOL, so someone help, please. At least so I can eat the right amount of protein.

message edited by GingerLeake


See More: How can I use a drop down list to populate other fields?

Reply ↓  Report •

#1
December 31, 2018 at 12:43:27
You will need to use a Named Range if you want to use two sheets.

On your first sheet,Sheet 1 select all your Items in column A, IE A2:A4
On the Ribbon, select the Formula Tab
Select Define Name
In the Name box enter you range name, IE Item
Leave Scope as Workbook
In the Refers To box it should already show your range of cells: =Sheet1!$A$2:$A$4
Click OK

On your second sheet,
On the Ribbon, select the Data Tab
Select cell C2
Select Data Validation
Under the Settings Tab,
In the Allow Box, change to read: List
In the Source Box enter: =Item
Click OK

In cell D2 enter the formula: =VLOOKUP(C2,Sheet1!$A$2:$E$4,2,FALSE)
In cell E2 enter the formula: =VLOOKUP(C2,Sheet1!$A$2:$E$4,3,FALSE)
In cell F2 enter the formula: =VLOOKUP(C2,Sheet1!$A$2:$E$4,4,FALSE)

You can change sheet names, just make sure you change all of the associated formulas.

There are additional settings you can make within the Data Validation wizard, like customize
Input messages or alerts.

See how that works for you.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
January 1, 2019 at 08:23:47
✔ Best Answer
Here's a New Years bonus.

Two alternative VLOOKUPS

The first one simply uses the COLUMN() function which will allow you to drag the formulas
along the row without having to manually change the column numbers,
so on Sheet 2, Cell D2 enter the formula

=VLOOKUP($C2,Sheet1!$A$2:$E$4,COLUMN(Sheet1!B2),FALSE)

Now just drag to the right two cells and the column number automatically increments as needed.

The second one, is actually my favorite.
It uses an Array Constant, which also means the formula must be entered as an Array Formula
using CTRL-SHIFT-ENTER

There is actually two versions, first version is:

=VLOOKUP($C2,Sheet1!$A$2:$E$4,{2,3,4},FALSE)

To use it, first select all three cells, D2,E2,F2
Then enter the formula in the formula box using CTRL-SHIFT-ENTER,
(Note the curly braces that surround the array constant)
and you get the Array Formula:

{=VLOOKUP($C2,Sheet1!$A$2:$E$4,{2,3,4},FALSE)}

Also, note the array constant uses the column numbers, on Sheet 1,
of the data you want to copy over, IE Columns 2, 3, and 4

The second variation is that the Array Constant can be a Named Range,
So, again, on the Ribbon, select the Formula Tab
Select Define Name
In the Name box enter you range name, IE Array_Nmbr
Leave Scope as Workbook
In the Refers To box enter your array constants: ={2,3,4}
Click OK

Now simply substitute the Name Range in the formula like:

=VLOOKUP($C2,Sheet1!$A$2:$E$4,Array_Nmbr,FALSE)

Don't forget to use CTRL-SHIFT-ENTER

The advantage of using the Range Name is, if in the future, you wish
to add additional columns to copy, you just change the Named Range
not the actual formula.

MIKE

http://www.skeptic.com/


Reply ↓  Report •
Related Solutions


Ask Question