Define Array from another cell?

[azaking]

Hey,

I think I am massively overthinking this whole process but I've spent a couple of hours trying to figure out what I thought would be a simple formula. All so my girlfriend and I can stop arguing over dinner.

I found a simply template online and wanted to make it a bit more useful by defining the categories and it updating dynamically rather than having 7 recipe boxes that only defined a particular day.

I have made the Category inputs drop down boxes which correlate to a "Recipe List" and when I change these I would like the Array in the "Meal" column to adjust along with it. For example When defining Chicken #1 it would choose a random meal from 'Recipe List' Chicken #1.

I have attempted to add in a small Vlookup for setting the array but again this would not dynamically change in the Meal forumular.

Please can someone put me out my misery.

Kind Regards
Aaron

 

[starl]

Without seeing your sheet, I don't understand what you're trying to do with the Vlookup, but your meal formula is close to what I think would work. I did this instead:
=OFFSET(A1, RANDBETWEEN(1,3),0)
with A1 being where Chicken #1 header is entered and 1,3 being the offset from A1 that would work for that Category

 

[jasonb75]

I think I am massively overthinking this whole process

I feel obliged to agree with you there ?
Start by dumping the 'Array' column, that isn't necessary and isn't actually providing you with any kind of array.

If there are always 10 recipes listed in each section then you could use something like

=INDEX($A:$A,XMATCH($E6,$A:$A)+RANDBETWEEN(1,10))

If the recipe count is variable then perhaps structured tables or named ranges would be a simpler option.

 

[azaking]

I feel obliged to agree with you there ?
Start by dumping the 'Array' column, that isn't necessary and isn't actually providing you with any kind of array.

If there are always 10 recipes listed in each section then you could use something like

=INDEX($A:$A,XMATCH($E6,$A:$A)+RANDBETWEEN(1,10))

If the recipe count is variable then perhaps structured tables or named ranges would be a simpler option.

I really was overthinking this!

Thank you so much, if only I get get them hours of my life back.

Appreciate your help and I have certainly learnt something.