Vstack Filter using indirect with constants and variables

[Mjk2117]

Hey everyone - looking for some help on this:

My dataset is in large table formats, with dozens of fields, two of which will need to be filtered the same way every time, which I have working well below:

VSTACK(FILTER(INDIRECT(B1),(INDIRECT(B1&"["&B2&"]")<0),FILTER(INDIRECT(B1),((INDIRECT(B1&"["&C2&"]")=C3)+(INDIRECT(B1&"["&C2&"]")=D3))*(INDIRECT(B1&"["&D2&"]")=B3))))

B1 = Table Name
B2,C2,D2 = Column Names
B3,C3,D3 = Filter Names

What I'd like to do and can't seem to figure out is how to add in additional fields that are interchangeable, but if do not cancel out the result of the above formula. Think of the above as constants, and what i'm trying to add as variables. I've tried nesting the above indirect snippets with Iferror and using a toggle cell with an IF statement for the variables i've mentioned but can't quite get it to work right.

Appreciate any help.

- Thanks

 

[Anonymous1378]

Consider posting some XL2BB of your current output and how you want it to appear? It's not clear to me what these "additional fields" may be. More table ranges? More rows from additional criteria? Header rows? More columns? I need something more specific than "additional fields".

 

[Mjk2117]

Okay so say it looks something like this. The constants are Status and Identifier, I have that part working. What I want to add in is the option to also filter it by Agency Name or Agent Type, for example, but if not using that, the original filter on the constants that my above formula does still applies.

 

[Anonymous1378]

You are already multiplying arrays within FILTER(), aren't you? Perhaps multiply it with an array that still returns everything if the "variable" is blank? Such as:

IF(F1<>"",(INDIRECT(B1&"["&C2&"]")=F1),1)

 

[Mjk2117]

You are already multiplying arrays within FILTER(), aren't you? Perhaps multiply it with an array that still returns everything if the "variable" is blank? Such as:

IF(F1<>"",(INDIRECT(B1&"["&C2&"]")=F1),1)

Got it, thanks! Turned into a lengthy one haha

=IF(E2="ON",VSTACK(FILTER(INDIRECT(B1),(INDIRECT(B1&"["&B3&"]")<0),FILTER(INDIRECT(B1),((INDIRECT(B1&"["&C3&"]")=C4)+(INDIRECT(B1&"["&C3&"]")=D4))*(INDIRECT(B1&"["&D3&"]")=B4)*(INDIRECT(B1&"["&E3&"]")=E4)))),VSTACK(FILTER(INDIRECT(B1),(INDIRECT(B1&"["&B3&"]")<0),FILTER(INDIRECT(B1),((INDIRECT(B1&"["&C3&"]")=C4)+(INDIRECT(B1&"["&C3&"]")=D4))*(INDIRECT(B1&"["&D3&"]")=B4)))))

 

[Anonymous1378]

If such a large chunk is repeated, perhaps consider this instead?

=VSTACK(FILTER(INDIRECT(B1),(INDIRECT(B1&"["&B3&"]")<0),FILTER(INDIRECT(B1),((INDIRECT(B1&"["&C3&"]")=C4)+(INDIRECT(B1&"["&C3&"]")=D4))*(INDIRECT(B1&"["&D3&"]")=B4)*IF(E2="ON",(INDIRECT(B1&"["&E3&"]")=E4),1))))

 

[Mjk2117]

If such a large chunk is repeated, perhaps consider this instead?

=VSTACK(FILTER(INDIRECT(B1),(INDIRECT(B1&"["&B3&"]")<0),FILTER(INDIRECT(B1),((INDIRECT(B1&"["&C3&"]")=C4)+(INDIRECT(B1&"["&C3&"]")=D4))*(INDIRECT(B1&"["&D3&"]")=B4)*IF(E2="ON",(INDIRECT(B1&"["&E3&"]")=E4),1))))

does the 1 reference the portion of the formula before the if statemetn?

 

[Anonymous1378]

1 is just equal to TRUE, so it shouldn't affect anything before the IF statement.