Using IF to conditionally LET/VSTACK

[Jedi Master]

=IF('Change Order Worksheet'!A50="","",LET(X,VSTACK('Change Order Worksheet'!A50:C73,'Change Order Worksheet (2)'!A50:C73,'Change Order Worksheet (3)'!A50:C73,'Change Order Worksheet (4)'!A50:C73,'Change Order Worksheet (5)'!A50:C73,'Change Order Worksheet (6)'!A50:C73,'Change Order Worksheet (7)'!A50:C73,'Change Order Worksheet (8)'!A50:C73,'Change Order Worksheet (9)'!A50:C73,'Change Order Worksheet (10)'!A50:C73),UNIQUE(FILTER(X,INDEX(X,,1)<>""))))

The above formula works perfectly and creates a filtered array from 10 worksheets. In each worksheet in cell P13 there is a Yes/No value. I need to make the above formula do exactly what it is doing now, but only if the P13 value in each of the 10 worksheets = "Yes". Can anyone please help me understand where and how to insert the values: (At least, I assume that is how I would have to do it?)

IF('Change Order Worksheet'!P13="Yes"
IF('Change Order Worksheet (2)'!P13="Yes"
ETC....

 

[DanteAmor]

Doubt:
If sheet2 has "Yes" and sheet5 has "Yes" and the other sheets have "No", do you want to filter only sheets 2 and 5?

Or only, if all the sheets have "Yes" then filter all 10 sheets, but if one sheet says "No", then don't filter.

By the way, you can abbreviate this part of the formula:

LET(X,VSTACK('Change Order Worksheet'!A50:C73,'Change Order Worksheet (2)'!A50:C73,'Change Order Worksheet (3)'!A50:C73,'Change Order Worksheet (4)'!A50:C73,'Change Order Worksheet (5)'!A50:C73,'Change Order Worksheet (6)'!A50:C73,'Change Order Worksheet (7)'!A50:C73,'Change Order Worksheet (8)'!A50:C73,'Change Order Worksheet (9)'!A50:C73,'Change Order Worksheet (10)'!A50:C73),UNIQUE(FILTER(X,INDEX(X,,1)<>"")))

like this:

LET(x,VSTACK('Change Order Worksheet:Change Order Worksheet (10)'!A50:C73),UNIQUE(FILTER(x,INDEX(x,,1)<>"")))

 

[Jedi Master]

Doubt:
If sheet2 has "Yes" and sheet5 has "Yes" and the other sheets have "No", do you want to filter only sheets 2 and 5?

Or only, if all the sheets have "Yes" then filter all 10 sheets, but if one sheet says "No", then don't filter.

By the way, you can abbreviate this part of the formula:

LET(X,VSTACK('Change Order Worksheet'!A50:C73,'Change Order Worksheet (2)'!A50:C73,'Change Order Worksheet (3)'!A50:C73,'Change Order Worksheet (4)'!A50:C73,'Change Order Worksheet (5)'!A50:C73,'Change Order Worksheet (6)'!A50:C73,'Change Order Worksheet (7)'!A50:C73,'Change Order Worksheet (8)'!A50:C73,'Change Order Worksheet (9)'!A50:C73,'Change Order Worksheet (10)'!A50:C73),UNIQUE(FILTER(X,INDEX(X,,1)<>"")))

like this:

LET(x,VSTACK('Change Order Worksheet:Change Order Worksheet (10)'!A50:C73),UNIQUE(FILTER(x,INDEX(x,,1)<>"")))

That abbreviation is awesome! Thank you for that. The intent is to create the array to be inclusive of all sheets marked yes. So in your example above, if sheets 2 and 5 are marked yes, then that would be the correctly created array Initially there will be no sheets marked yes, so no array will be created. Once I finish with this, I will need to reach out again, as I will have to take a formula I have that currently does the same thing with 50 different sheets and combine a version of this formula if we can get it working to create the array from those 50 sheets plus these 10 (those that are marked "Yes")

 

[DanteAmor]

So in your example above, if sheets 2 and 5 are marked yes, then that would be the correctly created array Initially there will be no sheets marked yes

Before applying the following formula, to make it shorter, ideally you would create 2 named ranges for each sheet.
For example, for sheet1, the named range for cell P13 would be p_1, for the same sheet1, the named range for cells A50:C73 would be co_1
for sheet2, the named range for cell P13 would be p_2, for the same sheet2, the named range for cells A50:C73 would be co_2
And so on...

In the following formula I put you up to sheet 4, do the same up to sheet 10:

=LET(x,APILARV(SI(p_1="Yes",co_1,""),SI(p_2="Yes",co_2,""),SI(p_3="Yes",co_3,""),SI(p_4="Yes",co_4,"")),UNICOS(FILTRAR(x,INDICE(x,,1)<>"")))

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Vogateer]

By the way, you can abbreviate this part of the formula:
...
like this:

LET(x,VSTACK('Change Order Worksheet:Change Order Worksheet (10)'!A50:C73),UNIQUE(FILTER(x,INDEX(x,,1)<>"")))

Very nice! I'm assuming that only works so long as there isn't some other sheet in between them.

In the following formula I put you up to sheet 4, do the same up to sheet 10:

=LET(x,APILARV(SI(p_1="Yes",co_1,""),SI(p_2="Yes",co_2,""),SI(p_3="Yes",co_3,""),SI(p_4="Yes",co_4,"")),UNICOS(FILTRAR(x,INDICE(x,,1)<>"")))

I didn't realize this was Spanish for a second and I was wondering where all these new formulas were coming from.

 

[DanteAmor]

Very nice! I'm assuming that only works so long as there isn't some other sheet in between them.

It's right

didn't realize this was Spanish for a second and I was wondering where all these new formulas were coming from

Sorry about that, I forgot to translate them

Here the correct formula:

=LET(x,VSTACK(IF(p_1="Yes",co_1,""),IF(p_2="Yes",co_2,""),IF(p_3="Yes",co_3,""),IF(p_4="Yes",co_4,"")),UNIQUE(FILTER(x,INDEX(x,,1)<>"")))

 

[Jedi Master]

It's right


Sorry about that, I forgot to translate them

Here the correct formula:

=LET(x,VSTACK(IF(p_1="Yes",co_1,""),IF(p_2="Yes",co_2,""),IF(p_3="Yes",co_3,""),IF(p_4="Yes",co_4,"")),UNIQUE(FILTER(x,INDEX(x,,1)<>"")))

The Spanish threw me for a loop for a min lol. Thank you so much!