Might be easier to add a column C with a formula such as:
=if(and(B1>eomonth(Sheet2!$A$2,-1),B1<=eomonth(Sheet2!$A$2,0)),"s",)
and apply a simplified query such as:
=query('Calculated Pay [view]'!B1:C1000,"Select B where C ='s' ")
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
Best Answer
You have 12 sheets for each month of the year. Each sheet has columns for the weeks in that month, each week running from Monday to Sunday. Updating monthly and weekly dates is a time-consuming task. You want to know whether there is a way that the year's start date can be nominated, and the weekly and monthly dates will automatically be updated.
The following answer uses a HELPER sheet for listing the weekly dates and their relevant month, and identifying which date is the first date for any given month. This data is then queried to create a 12 row list of the starting date for each month, the month number and the number of weeks in the month.
A sample "Month template" shows how values will update automatically depending on the month number entered.
HELPER SHEET (refer sample data)
Starting date:
Weekly Dates (Column A):
Manually populate the weekly dates in Column A:
Cell A6:
=A2
, Cell A7:=A6+7
, Cell A8-A57: copy formula from cell A7Month (Column B):
=month(A6)
, Cell B7-B57: copy the formula from Cell B6Unique/Duplicate (Column C):
=IF(COUNTIF($B$6:$B6,B6)=1, "Unique", "Duplicate")
, Cell C7-C57: copy the formula from Cell C6QUERY analysis(Column D/E/F)
=query({query({A6:C},"select Col1 where Col3 = 'Unique' label Col1 'Month Start'"), query({B6:B},"select Col1, count(Col1) where Col1 is not null group by Col1 label Col1 'Month', count(Col1) 'Num of Weeks in month'")}, "select Col1, Col2, Col3")
MONTHLY template
This describes how to create the sheet for Month#1; sheets for other months can be cloned from this.
=vlookup(B1,{HELPER!F3:F14, HELPER!E3:E14},2,false)
(format as "MMMM YYYY"). This looks up the month number in HELPER QUERYand returns the date value.=vlookup(B1,HELPER!F3:G14,2,false)
. Looks up the month number and returns the value for number of weeks in the month.Week#s - row 6
=if(B3=5,"Week#5",)
Dates - row 7
=B2
=B7+7
=if(B3=5,E7+7,)
Helper sheet - sample data
Sample Month template