Dynamically Add Sheets to a Query from a changing Range-CodePudding

On my spreadsheet, I have an "Organisation List" tab, "Schedule" tab and an increasing number of tabs per organisation, such as "BlueEdge Gym".

I have a QUERY formula that lists all dates from the different organisation's tabs schedules into one list on the "Schedule" tab. This makes a master schedule for me to view all organisations in chronological order, and I can see the next job.

Unfortunately, I have to manually add each organisations tab to this query in order to include them in the results.

I would like this to be dynamic from the organisation list, so that any organisation I add to my organisation tab list, will automatically/ dynamically be included into the list of sheets that the query on my schedule tab lists.

I would also like to have the Organisation name listed next to the date to which it corresponds, on the master schedule.

Here's the sheet:

CodePudding user response：

in A2 try:

=QUERY(SHEETNAMES(), "where not Col1 matches 'Schedule|Staff List|Organisation List'", 0)

and in E2:

=SORT(QUERY({
 IFERROR(INDIRECT(IF(A2="", 0, A2)&"!D:F"), {"","",""});
 IFERROR(INDIRECT(IF(A3="", 0, A3)&"!D:F"), {"","",""});
 IFERROR(INDIRECT(IF(A4="", 0, A4)&"!D:F"), {"","",""});
 IFERROR(INDIRECT(IF(A5="", 0, A5)&"!D:F"), {"","",""});
 IFERROR(INDIRECT(IF(A6="", 0, A5)&"!D:F"), {"","",""});
 IFERROR(INDIRECT(IF(A7="", 0, A7)&"!D:F"), {"","",""});
 IFERROR(INDIRECT(IF(A8="", 0, A8)&"!D:F"), {"","",""});
 IFERROR(INDIRECT(IF(A9="", 0, A9)&"!D:F"), {"","",""})},
 "where Col3=FALSE", 0), 1, 1)

CodePudding user response：

or try this more automated solution...

add script:

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName("Schedule");  
var src = sheet.getRange("A2");      // The cell which holds the formula
var str = src.getValue();
var cell = sheet.getRange("E2");     // The cell where I want the results to be
cell.setFormula(str);
}

and use this formula in A2:

=INDEX("=SORT(QUERY({"&JOIN(";", "'"&QUERY(SHEETNAMES(), 
 "where not Col1 matches 'Schedule|Staff List|Organisation List'", 0)&"'!D:F")&
 "}, ""where Col3 = FALSE"", 0), 1, 1)")