Comparing Forecasting vs Actual in changing column rows

[JohnMcAnallen]

Hi There,

It's my first time posting on the forum so apologies if I have posted this in the wrong place. 

I'm having a problem with a comparison workflow that I'm hoping someone can help with. 

Every week I get sent two files. One details forecasted hours, the other details actual hours and I have a workflow that analyses the sheets to spot the differences and produce one output file with various information included.

Example of Forecasted hours sheet:

Week 1
Unique Ref	Time (Hours) 3 Aug 2020	Time (Hours) 10 Aug 2020	Time (Hours) 17 Aug 2020	Time (Hours) 24 Aug 2020	Time (Hours) 31 Aug 2020	Time (Hours) 7 Sep 2020	Time (Hours) 14 Sep 2020	Time (Hours) 21 Sep 2020
1	0	0	0	0	0	0	37.5	37.5
2	0	0	0	30	30	0	0	0
3	37.5	0	0	0	0	0	0	0
4	0	0	0	30	0	0	0	0
5	0	0	0	0	0	0	22.5	0
6	0	30	37.5	0	0	0	0	0
7	30	0	0	0	0	0	0	0
8	0	0	0	30	37.5	37.5	7.5	0
9	37.5	37.5	37.5	0	0	0	0	0
10	0	0	0	22.5	0	0	0	0

Example of Actual Hours sheet: 

Unique Reference	Week Start Date	Total Hours Billed	Total Hours Not Billed	Total
1	17/08/2020		38	38
2	17/08/2020		23	23
3	17/08/2020		11	11
4	17/08/2020		1	1
5	24/08/2020		4	4
6	24/08/2020	14		14
1	24/08/2020		23	23
1	24/08/2020		1	1
1	24/08/2020	36		36
2	24/08/2020		2	2
3	24/08/2020	15		15
4	24/08/2020		11	11
2	24/08/2020		2	2

The issue I'm having is involving the naming of the forecasted hours sheet, as you can see from the second example below, the column names change every week to reflect the new weeks. This breaks formulas I have created to get the 8 week total for use in comparing against the actual hours sheet. 

Example 2 of Forecasted Hours:

Week 2
Unique Ref	Time (Hours) 10 Aug 2020	Time (Hours) 17 Aug 2020	Time (Hours) 24 Aug 2020	Time (Hours) 31 Aug 2020	Time (Hours) 7 Sep 2020	Time (Hours) 14 Sep 2020	Time (Hours) 21 Sep 2020	Time (Hours) 28 Sep 2020
1	0	0	0	0	0	0	37.5	37.5
2	0	0	0	30	30	0	0	0
3	37.5	0	0	0	0	0	0	0
4	0	0	0	30	0	0	0	0
5	0	0	0	0	0	0	22.5	0
6	0	30	37.5	0	0	0	0	0
7	30	0	0	0	0	0	0	0
8	0	0	0	30	37.5	37.5	7.5	0
9	37.5	37.5	37.5	0	0	0	0	0
10	0	0	0	22.5	0	0	0	0

My question is whether or not there is a way that I can create a dynamic total per unique reference regardless of the column name change every week?

Thanks!

[JosephSerpis · Accepted solution]

Hi  @JohnMcAnallen I mocked up a workflow that I think address the changing columns names and should be dynamic. Let me know what you think?

[JohnMcAnallen]

Thanks so much, this is perfect!