Below is a sample of the spreadsheet with the cells that matter.
It is the same columns per selected month.
In this case for the months of May 2016 to April 2017 (previous 12 months).
The file is generated as csv and mailed to me. The data I copy into de pre-defined table in this sheet.
In this example, row 31 contains the variables that were queryed containing the months (highlighted in yellow).
I quote this in cell C1 with the formula: = V.LOOKUP("Calendar Year / Month"; A:B; 2; FALSE) I have inserted rows 2 and 3 to generate the correct column header names.
Row 2 contains solid text that refers to the type given in the column.
Row 3 contains a formula that queries the month from cell C1 and merges with the value from row 2.
For example, the formula in row 3 for cell C3 is: = PART($C$1; 1; 3) & C2 => result is MEI_% (its Dutch for MAI).
This is the name I want to give to this column header for use in pivot tables.
Because the values shift each month, cell C3 will result in JUN_% next month.
One way to get the column headings right in the cells is the following:
The formula results of the cells C3 through C?? Paste as values in cells D3 through D??
That works, but if I can, I'd rather work faster and prevent mistakes.
For example, if you forget to copy the names to row 4, there are wrong names in the pivot tables and that is not the intention of course.
Hope this helps to get a solution.
If things are not clear, please let me know.
cheers, Jacques
|