Cell Value Change dependant on Product Type Selected

Hi there everyone

Am very new to this - not sure what is the right formula to use on this sheet -

In order to get the products from Sheet 3 into the yellow highlighted cells against the relevant numbers on sheet 2 - dependant on the Product Type on Sheet 1.

For example - if Sandwiches is selected on Sheet 1 - what formula would I require in cell B1 of Sheet 2 in order to display "Cheese" and then "Ham" in E1...etc
Then if the type of product changes on Sheet 1 the products on Sheet 2 change with it according to the data on Sheet 3.
Eg - selecting Drinks on Sheet 1 would make Sheet 2 cell B1 change to "Coke".

I really hope this makes sense to some one - sorry if it have not explained it very well. Many thanks!

You could use:

=INDEX(Sheet3!$B$2:$F$11,MATCH(Sheet2!AB$1,Sheet3!$A$2:$A$11,0),MATCH(Sheet1!$A$1,Sheet3!$B$1:$F$1,0))

or (if you don't want to see the zeros when there aren't 10 choices)

=IF(INDEX(Sheet3!$B$2:$F$11,MATCH(Sheet2!AB$1,Sheet3!$A$2:$A$11,0),MATCH(Sheet1!$A$1,Sheet3!$B$1:$F$1,0))=0,"",=INDEX(Sheet3!$B$2:$F$11,MATCH(Sheet2!AB$1,Sheet3!$A$2:$A$11,0),MATCH(Sheet1!$A$1,Sheet3!$B$1:$F$1,0)))

Somewhere on the sheet with the yellow boxes you need to have a cell with the word "Sandwiches" in it. This could be chosen from a drop-down list. Then, in the yellow boxes, you could use a VLOOKUP or INDEX/MATCH formula to extract the data from the appropriate list. If these lists were named ranges, you could make this happen easily using the INDIRECT function to reference the cell with the drop-down list. Hope this makes some sense to you!

I have just noticed your thread title. I (and kosherboy) should not have replied yet. Please go to your first post and amend the title to reflect the problem you have. Think of the sort of search terms you would use on Google to find an answer.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.


To change a Title go to your first post, click EDIT then Go Advanced and change your title.

Yep - that's fine. Many people use Google to search for solutions. If your title is meaningless - it's lost forever. Hence the rule. Anyhow - have you had a look at the solutions offered??

Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.