Help me please !!!!

[rothma]

I'm trying to create a workbook that will enable me to type in a text
string (entrees for food service in this case) and then use that string
to search another worksheet that contains all of the menu items and
their nutritional information and then to display the info on the first
worksheet.
In other words, if I type in Hamburger in a the 1st worksheet, I would
like the nutritional information (on a 2nd worksheet) to appear on the
1st worksheet automatically. I know this isn't especially challenging,
but I just can't figure it out. Thanks for any help you can give
me!!!!!!!
Matt

 

[ryanb.]

you can definitely do this a variety of ways. perhaps you could post a
sample of your data on the second sheet (say 5 rows)

I can then help you out using either lookups or index/match

Thanks,

ryanb.

 

[rothma]

Thanks Ryanb,
I've just realized something. Each item has a recipe# with it, which
is also listed in the nutritional information worksheet, so all that I
would really need to do is match the number from sheet1 to sheet2 and
then from that cell address, I could create relative references to
produce nutritional information on sheet1. However, I still cannot
figure out how to do this. I've attached a workbook with 2 sample
nutrional information listings and how I would like sheet1 to look.
Thanks again, I really appreciate it.
Matt

File Attached: http://www.excelforum.com/attachment.php?postid=331719 (nutritional info.xls)

 

[L. Howard Kittle]

Hi Matt,

Lets assume that on sheet 1, column A is a list of food items. A to Z,
apples to whatever food begins with Z and it takes up rows A2:a200.

On sheet 2 the same list is in column A and the nutritional info for each
food is in the respective row from column B through column G.

On sheet 1, select B2 through G2.
In B2, the cell that is in the Edit Mode, enter this formula:

=IF(ISNA(VLOOKUP(A1,Sheet2!A1:G200,{2,3,4,5,6,7},0)),"",VLOOKUP(A1,Sheet2!A1
:G200,{2,3,4,5,6,7},0))

While the cells are still selected, do an array entry. Hold down Ctrl +
Shift and then hit Enter. Again, while the cells are still selected go to
G2 and fill down the selected cells to G200. Click anywhere on the sheet to
de-select the cells.

Need more help? Post back.

HTH
Regards,
Howard

 

[L. Howard Kittle]

Hi Matt,

Attachments are frowned on deeply in this newsgroup. I don't believe it
came through, anyway. I will be glad to look at your workbook if you like
and my solution does not work out for you.

Regards,
Howard

 

[rothma]

My apologies Howard,
I can't quite get your formula to work. I've also changed around the
format of my workbook (see my 2nd post) and what I would like it to do.
If I shouldn't attach, I'm not quite sure how to show you my workbook.

Thanks for your help,
Matt

 

[ryanb.]

just copy and paste 5 rows of data from sheet 2 into your message

someone will show you how to do it without the array. Arrays are nice, but
they are extremely "touchy". It will be very easy.

Thanks,

ryanb.

 

[ryanb.]

ok... here it is... if you want an attachment of a workbook where it works,
email me and I will send it to you

on the MENU sheet

Assume row 1 is the header

A2= recipe #

Name
B2=IF(ISNA(MATCH($A2,'Sheet2 - Nutrional information'!$B$1:$B$8,0)),"NO
MATCH",INDEX('Sheet2 - Nutrional information'!$A$1:$J$8,MATCH($A2,'Sheet2 -
Nutrional information'!$B$1:$B$8,0),1))

Portion
C2=IF(ISNA(MATCH($A2,'Sheet2 - Nutrional information'!$B$1:$B$8,0)),"NO
MATCH",INDEX('Sheet2 - Nutrional information'!$A$1:$J$8,MATCH($A2,'Sheet2 -
Nutrional information'!$B$1:$B$8,0),3))

Calories
D2=IF(ISNA(MATCH($A2,'Sheet2 - Nutrional information'!$B$1:$B$8,0)),"NO
MATCH",INDEX('Sheet2 - Nutrional information'!$A$1:$J$8,MATCH($A2,'Sheet2 -
Nutrional information'!$B$1:$B$8,0),4))

Protein
E2=IF(ISNA(MATCH($A2,'Sheet2 - Nutrional information'!$B$1:$B$8,0)),"NO
MATCH",INDEX('Sheet2 - Nutrional information'!$A$1:$J$8,MATCH($A2,'Sheet2 -
Nutrional information'!$B$1:$B$8,0)+1,4))

Carbo
F2=IF(ISNA(MATCH($A2,'Sheet2 - Nutrional information'!$B$1:$B$8,0)),"NO
MATCH",INDEX('Sheet2 - Nutrional information'!$A$1:$J$8,MATCH($A2,'Sheet2 -
Nutrional information'!$B$1:$B$8,0),5))

D Fib
G2=IF(ISNA(MATCH($A2,'Sheet2 - Nutrional information'!$B$1:$B$8,0)),"NO
MATCH",INDEX('Sheet2 - Nutrional information'!$A$1:$J$8,MATCH($A2,'Sheet2 -
Nutrional information'!$B$1:$B$8,0)+1,5))

And so on and so on every other row ends the match function in +1

HTH,

ryanb.

just copy and paste 5 rows of data from sheet 2 into your message

someone will show you how to do it without the array. Arrays are nice, but
they are extremely "touchy". It will be very easy.

Thanks,

ryanb.

 

[Max]

The Nutritional Info sheet needs to be redesigned so that the data
for each recipe # is arranged in a single row, instead of the curent
way with "vertical-merged cells" and "double-stacking of data",
viz. to a structure like that indicated below:

In Sheet named: Nutritional Info
(Sample of redesigned data in A1:W3)
----------------------------------------------------------------------------
----------
Recipe
Name..............Recipe#...Portion...Calories...Carbo......Fat.......etc
Asian Bay Shakers.....16664...4 pieces....238.......21.1 gm...14.6 gm....etc
Baba Ghanoush............4441..........1 oz.....32.........3.1 gm.....2.2
gm....etc

With the above done, you could then in the Sheet named: Menu,
easily create and retrieve info based on Recipe#s entered in col A,
row 2 downwards using say, OFFSET & MATCH

Assuming the cols to the right of col A (Recipe#) in "Menu" sheet are
in the *same order* as that in "Nutritional Info" sheet, i.e.:

Recipe#...Portion...Calories...Carbo......Fat.........etc

Put in B2 (Under "Portion"):
=IF(ISNA(OFFSET('Nutritional Info'!$B$2,MATCH($A2,'Nutritional
Info'!$B:$B,0)-2,COLUMN()-1,1,1)),"-",OFFSET('Nutritional
Info'!$B$2,MATCH($A2,'Nutritional Info'!$B:$B,0)-2,COLUMN()-1,1,1))

Copy B2 across B2:V2 (where col V = rightmost col),
then copy down as many rows as you have data in col A

Note: The IF(ISNA(OFFSET(..),"-",OFFSET(..)) is an error handling construct
to ensure that invalid recipe#(s) will return "-" in cols B - V

hth
Max

 

[L. Howard Kittle]

Hi Matt,

Send it to my e-mail address, not the newsgroup.

Regards,
Howard

 

[ryanb.]

Max,
See my post... you can do it without changing the data. Sometimes your data
has to be a certain way for some other function. It definitely appeared to
me that the data was set up to print as a brochure. My formula works with
the data as is so there is no additonal setup time with the data.

Thanks,

ryanb.

ok... here it is... if you want an attachment of a workbook where it works,
email me and I will send it to you

on the MENU sheet

Assume row 1 is the header

A2= recipe #

Name
B2=IF(ISNA(MATCH($A2,'Sheet2 - Nutrional information'!$B$1:$B$8,0)),"NO
MATCH",INDEX('Sheet2 - Nutrional information'!$A$1:$J$8,MATCH($A2,'Sheet2 -
Nutrional information'!$B$1:$B$8,0),1))

Portion
C2=IF(ISNA(MATCH($A2,'Sheet2 - Nutrional information'!$B$1:$B$8,0)),"NO
MATCH",INDEX('Sheet2 - Nutrional information'!$A$1:$J$8,MATCH($A2,'Sheet2 -
Nutrional information'!$B$1:$B$8,0),3))

Calories
D2=IF(ISNA(MATCH($A2,'Sheet2 - Nutrional information'!$B$1:$B$8,0)),"NO
MATCH",INDEX('Sheet2 - Nutrional information'!$A$1:$J$8,MATCH($A2,'Sheet2 -
Nutrional information'!$B$1:$B$8,0),4))

Protein
E2=IF(ISNA(MATCH($A2,'Sheet2 - Nutrional information'!$B$1:$B$8,0)),"NO
MATCH",INDEX('Sheet2 - Nutrional information'!$A$1:$J$8,MATCH($A2,'Sheet2 -
Nutrional information'!$B$1:$B$8,0)+1,4))

Carbo
F2=IF(ISNA(MATCH($A2,'Sheet2 - Nutrional information'!$B$1:$B$8,0)),"NO
MATCH",INDEX('Sheet2 - Nutrional information'!$A$1:$J$8,MATCH($A2,'Sheet2 -
Nutrional information'!$B$1:$B$8,0),5))

D Fib
G2=IF(ISNA(MATCH($A2,'Sheet2 - Nutrional information'!$B$1:$B$8,0)),"NO
MATCH",INDEX('Sheet2 - Nutrional information'!$A$1:$J$8,MATCH($A2,'Sheet2 -
Nutrional information'!$B$1:$B$8,0)+1,5))

And so on and so on every other row ends the match function in +1

HTH,

ryanb.

just copy and paste 5 rows of data from sheet 2 into your message

someone will show you how to do it without the array. Arrays are nice, but
they are extremely "touchy". It will be very easy.

Thanks,

ryanb.

The Nutritional Info sheet needs to be redesigned so that the data
for each recipe # is arranged in a single row, instead of the curent
way with "vertical-merged cells" and "double-stacking of data",
viz. to a structure like that indicated below:

In Sheet named: Nutritional Info
(Sample of redesigned data in A1:W3)
-------------------------------------------------------------------------- --
----------
Recipe
Name..............Recipe#...Portion...Calories...Carbo......Fat.......etc
Asian Bay Shakers.....16664...4 pieces....238.......21.1 gm...14.6 gm....etc
Baba Ghanoush............4441..........1 oz.....32.........3.1 gm.....2.2
gm....etc

With the above done, you could then in the Sheet named: Menu,
easily create and retrieve info based on Recipe#s entered in col A,
row 2 downwards using say, OFFSET & MATCH

Assuming the cols to the right of col A (Recipe#) in "Menu" sheet are
in the *same order* as that in "Nutritional Info" sheet, i.e.:

Recipe#...Portion...Calories...Carbo......Fat.........etc

Put in B2 (Under "Portion"):
=IF(ISNA(OFFSET('Nutritional Info'!$B$2,MATCH($A2,'Nutritional
Info'!$B:$B,0)-2,COLUMN()-1,1,1)),"-",OFFSET('Nutritional
Info'!$B$2,MATCH($A2,'Nutritional Info'!$B:$B,0)-2,COLUMN()-1,1,1))

Copy B2 across B2:V2 (where col V = rightmost col),
then copy down as many rows as you have data in col A

Note: The IF(ISNA(OFFSET(..),"-",OFFSET(..)) is an error handling construct
to ensure that invalid recipe#(s) will return "-" in cols B - V

hth
Max

 

[Max]

Had read it earlier, Ryan, thanks.

It's about offering alternatives for the OP
- each alternative has its own pros & cons

Guess for me, what's important is that the
formulae, once set-up in a cell or 2, should be
easily propagated via copying across and down

Anyway, it's always good to know more options.

cheers
Max