# Dynamically creating column names based on data in another tab

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
``````

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?

• 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), ))`````` • 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)''"))`````` • to put it into correct tab (with script - manual recalculation):

``````function sheetName() {
}``````

``````=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))`````` • 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))`````` • This is tremendous! Thanks very, very much :-) Jul 5, 2019 at 9:00