setting up items list on menu costing template

[theboydoug]

Hi there,

I'm looking for a bit of help with the following.

I have a recipe costings template in excel. It's pretty basic... I just type in the ingredient, then the cost of the ingredient and the amount used, the portion size, wastage etc etc. It works well enough but if the price of a certain item goes up then I have to manually change every recipe.

I'm looking for a solution for this. What I need is to have a separate tab for an ingredients list so that when I start typing the ingredient into the costing template it looks for that word in the ingredients tab and when selected will automatically fill the item name and the cost of the item. That way, when I adjust the cost of an item in the ingredients tab it automatically adjusts every recipe tab too.

This will save a lot of time/mistakes/headaches!

I have attached my (very basic template) for reference

Thanks in advance

Dougie

[teylyn]

Hello,

the type-ahead functionality you describe will be a bit difficult to achieve. You may want to use a data validation with a list instead. Sort the list alphabetically for easier findability of ingredients. Then, once an item is selected, you can use Vlookup to return the unit and price for the ingredient.

cheers, teylyn

[theboydoug]

Thank you.

Could you give me an example of how to use Vlookup in this instance?

Kind regards

Dougie

[theboydoug]

I've got this far

=VLOOKUP("beetroot",ingredients!A1:B14,2,FALSE)

But, is there a way to make it that instead of "beetroot" I could put a formula in there that takes the information from another cell? I tried the one below but it didn't work...

=VLOOKUP("B13",ingredients!A1:B14,2,FALSE)

[teylyn]

Don't put quotes around the cell reference.

=VLOOKUP(B13,ingredients!A1:B14,2,FALSE)

[theboydoug]

Awesome.

Thank you so much, this is spot on... Just what I required. Much appreciated.