Help in Google Sheets for conditional formula

Anorion

Sith Lord
Staff member
Admin
Not sure if this is the right section lol.

So I need help with this sheet:

*i.imgur.com/PDw67hn.png

Notice that the values of A6 and C1 are identical.

What is going on here?
A1 to A3 have a Data Validation Form
A23 to I34 have the data
A5 to A10 is a list of items matching the required criteria, that is available.
B5 to B8 are 3 random items picked from A5 to A10, which will be served.
Notice that the formula in A6 is identical to the contents of C1. This has been manually entered in A6, but generated based on data validation forms in C1. In C1, it changes if different items are picked from the data validation form. Need to make it automatic in A6, based on the contents of C1. This is the point of the thread.

What are the formulas used?

A6

=FILTER(A25:A34, I25:I34="y", D25:D34 ="y", B25:B34 ="y")

B6
=ArrayFormula(Array_Constrain(vlookup(Query({ROW(A6:A10),randbetween(row(A6:A10)^0,9^9)},"Select Col1 order by Col2 Asc"),{row(A6:A10),A6:A10},2,FALSE),3,1))

C1
="=FILTER(A24:A34, "&B1&" ="&char(34)&"y"&char(34)&", "&B2&" ="&char(34)&"y"&char(34)&", "&B3&" ="&char(34)&"y"&char(34)&")"

B1
=HLOOKUP(A1,F23:I34, 2, FALSE)

What has already been tried?

=INDIRECT("B2")
and
=INDIRECT(B2)
both do not work.

=CELL(Contents, "C1")
and
=CELL(Contents, C1)
also both do not work

What do I really want to do?
I want a randomised subset of available items to be presented every day. It might be for an alt history RPG game in an imaginary restaurant.
PS, sorry, but the food items are also imaginary.
PPS, there is no political commentary here.
PPPS, this is also not about veg and non veg.

Anyone who wants to look at the sheet it can do so here.

NPCs will die of hunger, PLS HALP
 

Desmond

Destroy Erase Improve
Staff member
Admin
Can you tell me what is your end goal? Perhaps I can give a simpler solution.

Sent from my GM1911 using Tapatalk
 
OP
Anorion

Anorion

Sith Lord
Staff member
Admin
Yeah, I want a randomised subset of available items to be presented every day. The availability should be cross checked with multiple criteria. Basically, person can choose between three items, country, city size and location. This gives a subset, say A from a master list of available items. Then, a randomised subset from this list A is generated. All the person using the sheet has to do is to use the first three drop down boxes to arrive at a randomised list.
 

Desmond

Destroy Erase Improve
Staff member
Admin
IMO this would be easier to implement using an excel macro script than using only formulas. Possible but difficult.

Let me mull over this for a while and let me see if I can think of something.

Edit: So, if I understand correctly, you have a list of items and based on the location, you want to filter that list and give limited number of items?
 
Last edited:
OP
Anorion

Anorion

Sith Lord
Staff member
Admin
Yeah, prolly easier to do on Excel, but need to share doc, so GSheets

yes, and after the filter, randomise the list. But location has three criteria - country, size of settlement, and type of public space

think its just a matter of quotes and brackets somewhere tbh, or the right command, most of the stuff is done, just need to get that value in the cell to behave as a formula in another cell

its okay, don't try to find it for me if you don't know it outright, like to figure stuff out

thanks for the help, nonetheless
 

Desmond

Destroy Erase Improve
Staff member
Admin
country, size of settlement, and type of public space
I think you need to have three tables for each of these criteria. In otherwords, have a relationship between country and food, settlement and food and public place and food. Then, get a list of food items for a particular country, a list of food for a particular settlement type and a list of food for a particular public place type. Get an intersection of these lists and from this intersection, randomly choose a number of items.

I could write a python script to do this, but in Google sheets I will have to check.
 
OP
Anorion

Anorion

Sith Lord
Staff member
Admin
I think you need to have three tables for each of these criteria. In otherwords, have a relationship between country and food, settlement and food and public place and food. Then, get a list of food items for a particular country, a list of food for a particular settlement type and a list of food for a particular public place type. Get an intersection of these lists and from this intersection, randomly choose a number of items.

I could write a python script to do this, but in Google sheets I will have to check.

it took me a while to understand first para, but that is an alternative approach, will try that as well
 

patkim

Cyborg Agent
If I correctly understand, you have master tables in A23:I34, you will select County, City, Area in drop downs and then A6 and below should automatically give you the applicable filtered list and from that list random 3 items be selected in next column. If my understanding is correct then I am not too sure what's the purpose of Column C in your sheet.

However based on my above understanding, I took one approach based on 2 helper columns in Cols G & H and now both A6:A15 and B6:B8 are automated.

See the sample sheet here right now in Read Mode.
In your sheet you have entered entire table range for each column in B23:I34 table row 2, but it seems to me that this should be only a single column reference in each column.
Due to RANDBETWEEN the sheet will recalculate itself every time it's opened or modified and I guess Google sheets does not have Manual Calc option.
If you add more items to the master table, the formula needs to be updated to accommodate the new row references.
There are Data Validation drop downs in A1,2,3 however due to read only mode same will not be visible or editable and in my sheet.
I have modified the data (y,n values) in master tables in my sheet.
No much error correction or validations exists in the sheet right now.

Check out if this works for you and revert back.
 
OP
Anorion

Anorion

Sith Lord
Staff member
Admin
okay, first paragraph is a hard no, the drop downs are cosmetic and an indication of what I want to do.
Column c or c1 is the text generated based on the drop downs but A6 is hard coded. If c1 is executed as a command or formula in a6 problem solved.
I have no idea what “master table” means but guessing it’s the reference data

I thought the “recalculating” thing was a feature and not a bug and I built the whole thing around this recalculation

can understand new formula for new rows. That is not a problem. Think you might got have done something better with cross checking the values, so don’t even need that tbh. Rearranging or reassigning y and n is okay.

guess, have to think this through better before actually trying to do it.

thanks for the reply , it really broadened my options towards a solution
 
Last edited:
OP
Anorion

Anorion

Sith Lord
Staff member
Admin
Wow made a copy of your sheet
it works now, thanks!!

Im not understanding how it is working, but it is working lol, thank you so much
 

patkim

Cyborg Agent
Way this works is in following sequence…

1. User selects the options from A1 thru A3
2. That updates the required reference in B1 thru B3. HLOOKUP on B24:I24
3. B1, B2 & B3 are now fed to the FILTER Formula in A6 using INDIRECT to get a filtered list of applicable menu items where all 3 conditions are met (A1 thru A3 = y)
4. Now G1 holds the row reference from $1:$n where n= number of items returned by FILTER in A6. COUNTA(A6:A15)
5. This row ref in G1 is fed to an ArrayFormula using INDIRECT that now generates unique but random numbers between 1 thru n in H2 upto H11. (10 rows because you have 10 menu items as of now)
6. Formula now in B6, B7 & B8 is a simple INDEX to fetch the menu items from A6 thru An at numbered positions returned in H2, H3 & H4 which are anyways randomized and within 1 thru n.

The trick is to use INDIRECT to pass references from cells and also ArrayFormula manipulating ROW and RANDBETWEEN to generate unique but random numbers exactly from 1 thru n where n = number of items returned by the FILTER.

In future if you add more menu items in master table i.e. A34:I34 then make following changes.
e.g. if you add 2 more menu items in A35 & A36 then

1. Change manually entered references from B24 thru I24 e.g. B25:B34 becomes B25:B36
2. Formula in G1 to change from COUNTA(A6:A15) to COUNTA(A6:A17)
3. No change needed in H2 formula but drag it down now from H11 up to H13
4. If the master menu item list count goes > 17 then you will have to push the master table further down
 
OP
Anorion

Anorion

Sith Lord
Staff member
Admin
Oh god, thanks soo much. You have understood what I wanted to do better than me lol. I think I am beginning to understand a bit about about how all this works.
Really thanks for all the help, you answered my follow up question also.
I can see that you have taken the trouble to make the formula in H work for more items also, again cannot thank you enough for all those.
 
Top Bottom