From the question
Is it possible for creating the subset (filtered by Month) in a way that if we were to make changes in that subset view, those changes would cascade back to the Master. Would QUERY work? Or do I have to resort to a script. Or it is not possible.
Short answer
Google Sheets doesn't have a built-in feature that syncs two spreadsheets so you have to use something that extends it.
Explanation
Built-in functions like QUERY are not able to make make changes to another range. They could only return a value or an array of values. The same apply to custom functions.
Below is included a very simple Google Apps Script code example that exports the value of an edited cell to a cell of another spreadsheet. That code could be added to two spreadsheets to export the edited cells values to each other in order to have a "two way sync" between those spreadsheets.
It's purpose is to show that it's possible to sync two spreadsheets by using Google Apps Script. In order to make it work to sync a master sheet with filtered sheet to logic to find which rows are the match in the other file should be added. Usually this is done using an unique ID.
Broad instructions
- Copy the code to script projects bounded to the each spreadsheet and update the
targetID
and targetSheetName
variable definition code lines (lines 1 and 2).
- Add a On Edit installable triggers to each script project.
Code
var targetID = 'spreadsheet-id';
var targetSheetName = 'Sheet1';
function onEdit(e) {
// Get the event object properties
var range = e.range;
var value = e.value;
//Get the cell position
var row = range.getRowIndex();
var column = range.getColumnIndex();
exportValue(row,column,value)
}
function exportValue(row,column,value) {
var ss = SpreadsheetApp.openById(targetID);
var s = ss.getSheetByName(targetSheetName);
var target = s.getRange(row, column);
target.setValue(value);
}
Limit the changes to be synced
In order to limit the changes to be synced add rules to the code. The JavaScript comparators ==
, ===
, !=
, !==
, >
, <
, >=
, <=
, and the logical operators &&
, ||
, !
with if
, switch
among other commands could be used to create simple of complex rules.
References
The command
=query(A7:F, "select A, F where F is not null order by A desc limit 30")
returns the last 30 values where F is nonempty. If this command is placed, say, in G1, then it fills the array G1:H30, to which you can then refer:
=DATEVALUE(TODAY() + ((108.36297798)-(INTERCEPT(H1:H30, G1:G30)))/(SLOPE(H1:H30, G1:G30)))
In principle, one can avoid creating a helper array and have just one formula returning the result. This requires replacing the references to A with
=query(A7:F, "select A where F is not null order by A desc limit 30")
and the references to F with
=query(A7:F, "select F where F is not null order by A desc limit 30")
The result is kind of long and scary but it works.
=DATEVALUE(TODAY() + ((108.36297798)-(INTERCEPT(query(A7:F, "select F where F is not null order by A desc limit 30"), query(A7:F, "select A where F is not null order by A desc limit 30"))))/(SLOPE(query(A7:F, "select F where F is not null order by A desc limit 30"), query(A7:F, "select A where F is not null order by A desc limit 30"))))
Best Answer
I would suggest that you look into the NETWORKDAYS.INTL function, which allows you to find the difference between two dates accounting for standard, customized weekend days off and for any additional list of holidays or days off. For instance, assuming your past date were in cell A1:
The plain-English translation here is "Find the workdays that happened between the two dates, given that every Sunday was a day off and additionally excluding any dates listed in DaysOff!A2:A."
The string "0000001" defines what your regular weekend-days-off are, starting with Monday.
0 = "not counted as a weekly day off" 1 = "counted as a weekly day off"
Then you'd just setup a sheet (I suggest DaysOff! here) where you list any additional holidays and such that should not be counted in the total day count between the dates but which are not regular weekly occurrences.