Searching For Text Across Different Sheets

[jspruill]

I’m trying to find the right formula for adding values across different sheets after it finds the right text.

From the attached spreadsheet, the formula I want to change is on “Wk1 Shopping List” in column C.

My problem is that the values I have in column A on the FoodCosts sheet move around when I add new items, so I constantly have to change the formula on “Wk1 Shopping List” in column C each time this happens.

Is there a way I can tell it to search for a certain text from column A on sheet “FoodCosts” (ex. “Avacado”) and once it is found, to then take the value from column D on that row and multiply it by the quantity specified in column B on sheet “Wk1 Shopping List”?

Thanks.

[FDibbins]

As long as you keep the names the same in each sheet, the following formula will work...

=VLOOKUP(A2,FoodCosts!$A$2:$D$22,4,FALSE)*'Wk1 Shopping List'!B2 copy this to all the cells in wk1 shopping list that you need it.

When you start copying, note that the very 1st item in your list (and the very last 1 too), return a #NA value. This is because the names (apples - lb and apples/lb) dont match. If you want to ensure that the names are exactly the same, you (or I can, if you dont know how) can create a dropdown list on the 2nd sheet, based onthe names in the 1st sheet.

Hope this helps, let me know if you need anything else?

[jspruill]

Great! That's perfect. Thank you.

No, I'm not sure how to create a drop down list. I was just going to make sure everything matched perfectly.

[FDibbins]

Start with creating a named range on your items page, highight the range, right click and select "name a range". If you will be adding to the list, make sure the range is long enough to accomodate the growth, but you can always change the range later if needed. I made a range called "Items" in the attached sheet, from A2-A50

Then in your Shopping list sheet, apply data validation to where you will be entering your data.../data, data toold, data validation, under "allow", select "list" and type inthe range name (in this case, I used =Items). Do this for all ranges where you will enter in your shopping list. Once you have done that, when you click on that cell, you will notice a drop-down at the top right of the cell, if you click on that, it will show you all the items in your (named) FoodCosts sheet

I have attached a sample based on your info, hope this helps

[jspruill]

Great! Thank you so much for your help.