Auto populate data from master worksheet to cooresponding .monthly spreadsheet

[Rocky Mountain High]

I have a master workbook (or worksheet) where data is entered on a daily basis. Column D is a chronological order of daily case numbers. Column B is the date when each case number was issued. There are times when the dates may not be in chronical order due to the data being collected and entered for two separate municipalities. Monthly & quarterly sheets are created from the mastersheet to include formulas and formatting. I can't figure out a formula that would filter data by month and (also keeping the data from Column D in chronological order) and automatically populate the data into the appropriate monthly spreadsheet. I currently have to first filter the data from the master worksheet and copy/paste it to the corresponding monthly spreadsheets. This often leaves too much room for errors and it sometimes messes up my formulas and formatting. I can send a sample of my current master & monthly sheets but it's sensitive data so I don't want to put it on a public forum.

 

[arun_eagertolearn]

Hi

Please check the following approach
Please make individual sheets with the month names- eg: Jan 2024, Feb 2024 etc

Within any individual sheet (say Jan 2024), please take out the sheet name using the following formula in any cell (Say A1)
=MID(CELL("filename"),SEARCH("]",CELL("filename"),1)+1,LEN(CELL("filename")))
This would yield the sheetname, ie, Jan 2024 in this case in the cell

Next, please make this string "Jan 2024" into a date, using the formula datevalue(A1)

With this date in A2, please use the following formula
(Assuming the source data resides in "Master" sheet ,across columns A:D)
B1= filter('Master'!A:D, date(year('Master'!B:B), month('Master'!B:B),1)=A2)

OR

to combine everything in one go

B1= filter('Master'!A:D, date(year('Master'!B:B), month('Master'!B:B),1)=DATEVALUE(MID(CELL("filename"),SEARCH("]",CELL("filename"),1)+1,LEN(CELL("filename")))))

 

[arun_eagertolearn]

Hi

Another approach

Please add a helper column in the master data which would have the month

Please make a pivot table, with the the new month column as page filter, and the requisite data (date, serial no, other details) in the pivot fields

Then, Tabs--->Pivot Table Analyze---->Pivot table (dropdown)----> Options (dropdown)---->Show Report filter pages

This creates individual pivot tables for each of the months, with sheet titles also labeled appropriately