IFS statement with multiple nested QUERY functions
With a fair amount of help from SO folks, I managed to put together about 15 QUERY tabs used to extract necessary data for reporting.
In order to make it tidy, I'd like to enclose all these queries in an IFS statement referring to a Data Validation cell and run the QUERY by simply changing the value from the validation cell.
I managed to get it running with no errors, however, I get a single cell output.
What I am using for this is:
=IFS('Validation'!$A$1 = "Giberish",
{QUERY('All'!$A$5:$AP,
"SELECT AJ, '" & IDBlah & "', 'Blah'
WHERE AJ IS NOT NULL
LABEL '" & IDBlah & "' 'ID',
'Blah' 'BlahBlahBlah'",1)},
'Validation'!$A$1 = "GiberishGiberish",
{QUERY('All'!$A$5:$AP,
"SELECT AJ, 'WCaa', 'Blah'
WHERE AJ IS NOT NULL
LABEL 'WCaa' 'BlahBlah',
'Blah' 'BlahBlahBlah'",1);
QUERY('Upload'!$A$2:$AL,
"SELECT A, 'WCaa', 'Blah'
WHERE A IS NOT NULL
LABEL 'WCaa' '',
'Blah' ''",0)} )
I tried to enclose it all in an ARRAYFORMULA, but I get the same single cell output, with no error.
What is the correct approach?
Let me know if you need dummy data, but have in mind that I have corporate security policies that restrict me from sharing any sheet to anyone outside of the company.
you will need to use IF instead of IFS like:
=IF(Validation!$A$1 = "Giberish", {QUERY(All!$A$5:$AP,
"SELECT AJ,'"&IDBlah&"','Blah'
WHERE AJ IS NOT NULL
LABEL '"&IDBlah&"''ID','Blah''BlahBlahBlah'",1)},
IF(Validation!$A$1 = "GiberishGiberish", {QUERY(All!$A$5:$AP,
"SELECT AJ,'WCaa','Blah'
WHERE AJ IS NOT NULL
LABEL 'WCaa''BlahBlah','Blah''BlahBlahBlah'",1);
QUERY(Upload!$A$2:$AL,
"SELECT A, 'WCaa','Blah'
WHERE A IS NOT NULL
LABEL 'WCaa''','Blah'''",0)}, ))
when IFS is in a combination of ARRAYFORMULA it gains a special skill which requires an array to be inputted in order to output an array again.