vlookup on list

[Colby]

what I am trying to build is a calorie counter for some clients and
friends. I have one tab that contains the recomended food
and the corresponding facts. what I would like to happen is for the
user to be able to enter food into the cell and then have the
formula obtain the correct values and add if there is more than
one item in the list.



Cal Pro Carbs
granola bar, yogurt, banana 475 10 93
protein bar, salmon, 190 12
tuna, baked ham wrap, protein bar 90

what I am tryin to do is get the formula in the 'Cal' cell to do a vlookup
to a table in another sheet for all items in the list. I am having trouble
figuring out how to get the second and or third item in the list using vlookup.
I know vba for access but unsure in excel.
Any help is greatly appreciated.

 

[ajetrumpet]

tough questions. check this out:

http://www.ozgrid.com/forum/showpost.php?p=422403&postcount=2

 

[Colby]

ajetrumpet, 'preciate the response. I will see if I can massage that code
enough to give me something.

 

[lemo]

my vba is very limited, but i know how to do it with formulas, if that helps. it's a bit bulky.

basic idea would be, somewhere to the right or on another sheet or on the sheet where you have the master table of Cal Pro Carbs values, to -

look for comas using FIND, e.g. FIND(",",$A1), FIND(",",$A1,F1+1), FIND(",",$A1,G1+1) where F1 is the location of the previous comma, etc.;

using locations of the commas, isolate different foods consumed at one meal, e.g. LEFT($A1,F1-1), MID($A1,F1+2,(G1-F1-2), etc.;

do a VLOOKUP of the values using the location of the commas, e.g. VLOOKUP(L1,CalProCarbs,2,FALSE), VLOOKUP(M1,CalProCarbs,2,FALSE), etc.;

all while taking care of errors using IFERROR or ISERROR;

sum them up and you are done.

if nobody suggests more elegant solution, let me know if you'd like more details, maybe a little sample file.

good luck,
l

 

[chergh]

Well you would need to use the split function to get each item and it would of course depend on the person typing in the items correctly. It would be something like this:

sub bleh()

dim xCal as long
Dim xPro as long
dim xCarbs as long

dim ws1 as worksheet
dim ws2 as worksheet
dim wb as workbook
dim strArr as variant

set wb = thisworkbook

set ws1 = wb.worksheets("Sheet1")
set ws2 = wb.worksheets("Sheet2")

strArr = split(ws1.range("I8").value, ",")

xCal = 0
xCarbs = 0
xPro = 0

for i = lbound(strArr) to ubound(strArr)
   'this bit here assumes that Cal info is one column to the right of the item name
   ' two columns to the right for Carbs and 3 columns for Pro
   'change the value in the offset bit to represent how your actual data is structured
   xCal = xCal + ws2.Cells.Find(strArr(i), LookIn:=xlValues).Offset(0, 1).Value
   xCarbs = xCarbs + ws2.Cells.Find(strArr(i), LookIn:=xlValues).Offset(0, 2).Value
   xPro = xPro + ws2.Cells.Find(strArr(i), LookIn:=xlValues).Offset(0, 3).Value
next i

ws1.range("K8").value = xCal
ws1.range("L8").value = xPro
ws1.range("M8").value = xCarbs

end sub

This is the general idea of how to do it. See what you can do with this and post back if you need any assistance.

 

[Colby]

thanks for all the suggestions. I have been unable to work this project
but hope to hit it tonight. Will post back after attempts will all suggestions.

lemo - if you have a sample wb that would be great.

chergh - for a general idea that looks pretty robust.

ajetrumpet - I was unable to massage that code to work - I am sure it
is due to my limited knowledge of excel vba.

 

[lemo]

here it is.
** sample ** - make sure you quality test it if you opt to use it.
assuming no more than 5-course meals.
l