list pulldown selection returns multiple columns from a row - how?

[mcascone]

I've been looking around here for a while and i don't think this
particular issue has been posted, forgive me if it has.

I'll try to be as descriptive as possible.

I'm trying to make a nutrition journal that will use a list of foods
and their associated nutrition values to track daily eating habits.

I'd like to be able to have a column of cells that each have a pulldown
list with the names of the foods. When a food is selected, I want all
of the associated values to be placed in the columns in the first
cell's row. Over the course of a day, the user enters the foods eaten
in the first column, and excel populates the protein, carbs etc in the
rows, and totals them at the bottom. One row per food eaten.
Essentially, i need it to use the list as a database and run queries on
it, but i think setting up a db would be even more complicated, if I
can just get this function figured out. (But any db suggestions would
be great! I'm running a Mac at home and NT at work.)

I've gotten as far as creating the pulldown list and using it in a
separate sheet (in the same file). I can't figure out how to create a
function that returns more than one column of data.

A wish-list feature would be to be able to add new foods and their
values in the user's sheet that then get added to the main list, but
for now adding them to the main list - so they show up in the pulldowns
- is fine. What gotchas do I have to look out for when adding to the
list? Will all of the functions automatically update themselves; if I
have it as a reference that should work right?

Thanks in advance for your help! Let me know if I need to clarify
anything.

 

[Peo Sjoblom]

Use Vlookup

=IF(A2="","",VLOOKUP(A2,Table,{2,3,4,5},0))

where 2,3,4,5 are the columns, note that you have to select 4
cells and enter the formula with ctrl + shift & enter

 

[mcascone]

Thanks so much for your quick reply! I'm having trouble getting it t
work though.

In your supplied formula, A2 is the cell with the pulldown? O
something else?

"Table" should be replaced with what? The name of the list - in my cas
"food"?

The columns should be in the form C, D, E or Protein, Carbs, Calories?

I'm asking these questions because I keep getting an error message whe
I ctl-shft-enter the formula.

I'm new at this! Explain it like I don't know what I'm doing - 'cause
don't!

Thanks so much!!

Ma

 

[Peo Sjoblom]

Thanks so much for your quick reply! I'm having trouble getting it to
work though.

In your supplied formula, A2 is the cell with the pulldown? Or
something else?

Yes it is the cell with the dropdown

"Table" should be replaced with what? The name of the list - in my case
"food"?

Replace "Table" with either a defined name of the table or it's dimension,
i.e.
If the range is H2:L50 you can replace "Table" with the range

The columns should be in the form C, D, E or Protein, Carbs, Calories?

It doesn't matter. But I read your question like that when you select a
value from the dropdown it will look
up the same value from a list and then a certain number of values from the
same row

I'm asking these questions because I keep getting an error message when
I ctl-shft-enter the formula.

I'm new at this! Explain it like I don't know what I'm doing - 'cause I
don't!

Assume you want the values from 3 columns to the right of the lookups in
your table
Then you have to select 3 cell (click in for instance B2 and hold down while
you select
3 columns (B2, C2 and D2), now they will be highlighted and if you started
selecting from B2 it will be "white" while the other 2 are bluish
While selected click in the formula bar and type in the formula, then
with the cursor still in the formula bar press
ctrl + shift & enter simultaneously. If you do it correct excel will frame
it with
brackets {formula}

 

[mcascone]

I'm still getting #REF! in the three destination cells.

My formula looks like:
=IF(A32="","",VLOOKUP(A32,A2:A25,{3,4,5,6},0))

There is a row in between the list of food names and their values, so
increased the values in the bracketed part of your equation by one

 

[Frank Kabel]

Hi
you first have to select 4 cells and enter the formula with
CTRL+SHIFT+ENTEr. Also change the formula to
=IF(A32="","",VLOOKUP(A32,A2:F25,{3,4,5,6},0))

 

[mcascone]

That works! That is so cool! You spotted the problem - my range wa
A2:A25, it should have been A2:F25. With that change, the lookup work
perfectly!!! Thanks!