Migrate row when adding one word

[Headchefski]

I am writing and costing recipes, I have a liast of ingredients on one page in excel, these have amounts and cost in the row with them eg; Rice 1 kg 0.23
I want to type rice in my recipe and the rest of the row migrates automatically, is this possible?
I am only a basic user, not sure how to put in formulas and things, so if you can help please make it sound as easy as possible. Thank you

[Richard Buttrey]

Hi and welcome to the forum

Yes, of course it's possible. Probably with a VLOOKUP() formula.
If you need more specific help you'll need to upload your workbook along with some notes that explain which cells are your basic data, and which are your results cells. Manually add some example results so that we can understand exactly what you want.

[Headchefski]

Book2.xlsx
Here is an attachment of what I would like to do.
I woul like to be able to type the uingredient and the Measure Nad Price will come with it. I would like to be able to this on many tabe for different dishes.
Thank you for your help.

[Richard Buttrey]

Hi,

See attached

[RobertMika]

=iferror(vlookup(a5,'bread products'!$b$4:$d$36,3,0),0)
Book2(1).xlsx

[Headchefski]

That looks spot on, thank you, is it possible to bring over the Measure aswell? Thank you

[Headchefski]

Now that is fantastic, if I keep adding ingredients to list will this still work or will it need to be formulated again?

[Richard Buttrey]

@Headchefski

No, you can freely add and delete ingredients/products as you wish. The range of cells that covers the list has been given what's known as a dynamic range name (Products), so you'll always see whatever has been entered on the products sheet.

[RobertMika]

It does need to be formaulated as it covers whole columns in Bread PRoducts tab

What does your formula in C2 supposed to do?
Currently you have got an error?


Richard,
What does this part do:
ROW('BREAD PRODUCTS'!$B$3)

Never seen this before.

[Richard Buttrey]

@Robert

That Row() formula simply returns the row number for B3 in the Bread Products sheet. I tend to use something like that rather than hard coding the value 3 in this case, just in case the user adds additional rows above the table.

I think it's a moot point whether when using INDEX it's better to index just the table in question or the whole range of used cells starting at A1. Must admit that I generally (although not here) tend to use the latter method and similarly in the MATCH element, match on the whole column so that there's no need to worry about the index table being positioned differently.

However since I'd defined the products range for a drop down and didn't want spaces or column labels in the drop down I stayed with the indexing table method and adjustment for the top row of the table rather than just indexing A1:B37