0

I am using Google Sheets, and my columns are set up for each month (Jan, Feb, March, etc). I have several rows that are pulling data from other tabs in the sheet to create an at a glance master list. I would like these rows to freeze on the last day of the month. ie. certain rows in the Jan column stop updating after Jan 31st. The February column stops updating after February 28th, etc.

The idea is that this master list can show me what the total was from the other tabs on the last day of the month. Any suggestions on what script I can use to accomplish this?

Here's an example spreadsheet that shows how the data is taken from other tabs and put into the masterlist: https://docs.google.com/spreadsheets/d/1qRVA6r_TWDWM3wmSvMdG5H5NmJ7-3rvJ9dx15mUo6rc/edit#gid=0

Improve this question
3
  • Welcome to Web Applications. Questions on this site should be specific, so please avoid to use phrases like "any suggestions...." as this might make the question to look as primarily-opnion based question which are off-topic. Instead, show what you tried and tell where exactly do you get stuck and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén ♦
    Nov 3, 2021 at 15:13
  • Related 1, 2
    – Rubén ♦
    Nov 3, 2021 at 16:41
  • See What is the XY problem?
    – doubleunary
    Nov 4, 2021 at 5:58

1 Answer 1

Reset to default
1

You want to summarise results from various sheets by "freeze[ing rows] on the last day of the month". I respectfully suggest that another, possibly more conventional, way to express this is that you want to summarise results from various sheets by month (by date). This goal is easily achieved but it requires reformatting of your source data so that these reports are easier to create.

Date Headers on 'Master Sheet 21/22'

At present these are simply text (Oct, Nov, Dec, etc). These need to be dates.

  • Cell B2 - enter the date (mm/dd/yyyy for the USA) for the first month of your analysis. For example, I entered 10/1/2020 = 1 October 2020.
  • Cell C2 - enter =EDATE(B2,1) and then copy the formula across as many columns in row 2 as appropriate. This formula will take the date in the left adjacent cell, and calculate and display the start date for the next month.
  • Row 2 (starting at Column 2) - format the cells as "MMM-YY"

Date Values on "Event Participants" (and other sheets)

The "date" values in Column A are text but they need to be actual dates. For example, "July 13th" should be entered as 13/7/2021 (US format). If you wish, you can modify the cell format later to match your text format.

Master Sheet 21/22

  • Delete any existing content in the range B4:P5 (or further to the right as necessary)

  • Enter this formula in cell B4:

=iferror(transpose(query('Copy of Event Participants'!$A$3:$D, "select count(B), sum(D) where A is not null and A >= date '"&TEXT(B2,"yyyy-mm-dd")&"' and A <= date '"&TEXT(eomonth(B2,),"yyyy-mm-dd")&"' label count(B) '', sum(D) '' ")),)

  • Copy the formula across as many columns in row 4 as appropriate.

  • The formula will populate both row 4 and row 5 (number of activities and number of participants). Where there is no data to report, the cells will be blank. You could change this to a zero if you wish.

Event Participants (Summary)

You have an area in rows 16&17 to summarise results. This is OK as it stands, but the likelihood is that more transaction details will be added and this location for the summary is a barrier to an efficient analysis. Please delete the existing summary rows.

This analysis can be moved to another location. In the example below I have put it at the top of the sheet, adjacent to the transactions, but you could choose another place (or another sheet) if you wish. (just not in Columns A:D)

  • Headers (F1:H2) - this is text; manual data entry
  • Formula - Enter this formula in Cell F3: =query({A3:D,ArrayFormula(if(A3:A="",,EOMONTH(A3:A,-1)+1))},"select Col5,count(Col2), Sum(Col4) where Col1 is not null group by Col5 order by Col5 asc label count(Col2) '',Sum(Col4) '' format Col5 'MMM-yyyy' ",0)

Master Sheet

Master

Event Participants and Analysis

screenshot

Other Sheets: Media, membership Sponsors, etc

In this answer, I have only addressed "Event Participants" since this is the only sheet containing actual data. However, the issues mentioned here also apply to those other sheets.

  • change text-dates to actual dates and
  • move the summary/analysis away from the bottom of Column A:D

The formula for the Master sheet can be adapted to the information of each relevant sheet. Ditto the Analysis/Summary.

Improve this answer
2
  • Great answer to this XY question! Perhaps you could highlight that the problem to solve is not "how to freeze" but "how to re-format the source data so that reports are easier to create."
    – doubleunary
    Nov 4, 2021 at 5:57
  • Indeed. The question title itself is misleading. Thanks for the suggestion - I'll put it in hand.
    – Tedinoz
    Nov 4, 2021 at 7:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.