Google-sheets – Dynamically creating column names based on data in another tab

google sheetsgoogle-sheets-arrayformulagoogle-sheets-datesgoogle-sheets-queryvlookup

I have a Google sheet that logs all submissions on my website of various contact forms. I need to know how many times each contact form has been submitted, and in which week they were submitted.

The goal is to have a Google sheet to look like this:
(Each tab in the Google sheet is the same name of these forms)

Week Commencing | Number of Submissions
  1st July 2019 | 37
  8th July 2019 | 7

The data I have looks like this:
(in a tab named "data")

form name                         | date 
Custom Printed Premium Gift Boxes | 7/4/2019 12:01:28

Link to google sheet: https://docs.google.com/spreadsheets/d/1ffOVvGT7CGV3gXR1HP5kp6Pg1JgD40qRjlt6lwwFbo0/edit?usp=sharing

My questions:

  1. How do I count the submissions from the data tab and put the total, by the week, into the correct tab?
  2. How do I get the week number, convert it to a correct row title of "1st July 2019" and then have the number of submissions for that very week go into the correct cell?
  3. Is there a better way to achieve what I'm doing? Am I square peg/round hole-ing?

Best Answer

  • to derive week number:

    =ARRAYFORMULA(IF(LEN(B2:B), WEEKNUM(B2:B, 2), ))

    0

  • to convert it into the 1st day of each week number:

    =ARRAYFORMULA(IF(LEN(B2:B), VLOOKUP(WEEKNUM(B2:B, 2), 
 {WEEKNUM(ROW(INDIRECT("A"&DATEVALUE(MIN(B2:B))-7&":"&
                           DATEVALUE(MAX(B2:B)))), 2),
     TEXT(ROW(INDIRECT("A"&DATEVALUE(MIN(B2:B))-7&":"&
                           DATEVALUE(MAX(B2:B)))), "d. mmmm yyyy")}, 2, 0), ))

    0

  • to create full summary table:

    =ARRAYFORMULA(QUERY(IF(LEN(B2:B), {VLOOKUP(WEEKNUM(B2:B, 2), 
 {WEEKNUM(ROW(INDIRECT("A"&DATEVALUE(MIN(B2:B))-7&":"&
                           DATEVALUE(MAX(B2:B)))), 2),
     TEXT(ROW(INDIRECT("A"&DATEVALUE(MIN(B2:B))-7&":"&
                           DATEVALUE(MAX(B2:B)))), "d. mmmm yyyy")}, 2, 0), A2:A}, ),
 "select Col2,Col1,count(Col1) 
  where Col1 is not null 
  group by Col1,Col2 
  label count(Col1)''"))

    0

  • to put it into correct tab (with script - manual recalculation):

    function sheetName() {
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

    =ARRAYFORMULA(QUERY(QUERY(IF(LEN(data!B2:B), 
{VLOOKUP(WEEKNUM(data!B2:B, 2), 
 {WEEKNUM(ROW(INDIRECT("A"&DATEVALUE(MIN(data!B2:B))-7&":"&
                           DATEVALUE(MAX(data!B2:B)))), 2),
     TEXT(ROW(INDIRECT("A"&DATEVALUE(MIN(data!B2:B))-7&":"&
                           DATEVALUE(MAX(data!B2:B)))), 
 "d. mmmm yyyy")}, 2, 0), data!A2:A}, ),
 "select Col2,Col1,count(Col1) 
  where Col1 is not NULL 
  group by Col1,Col2 
  label count(Col1)''", 0), "select Col2,Col3 where Col1='"&SHEETNAME()&"'", 0))

    0

  • to put it into correct tab (without script - manual input)

    =ARRAYFORMULA(QUERY(QUERY(IF(LEN(data!B2:B), {VLOOKUP(WEEKNUM(data!B2:B, 2), 
{WEEKNUM(ROW(INDIRECT("A"&DATEVALUE(MIN(data!B2:B))-7&":"&
                       DATEVALUE(MAX(data!B2:B)))), 2),
 TEXT(ROW(INDIRECT("A"&DATEVALUE(MIN(data!B2:B))-7&":"&
                       DATEVALUE(MAX(data!B2:B)))), 
"d. mmmm yyyy")}, 2, 0), data!A2:A}, ),
"select Col2,Col1,count(Col1) 
where Col1 is not NULL 
group by Col1,Col2 
label count(Col1)''", 0), "select Col2,Col3 where Col1='Contact Form'", 0))

    0

Related Topic