Hi,
I think quite tricky situation and I may be going about it the wrong way.
I have 3 sheets.
Sheet1 has a value in C3 which is my week number, 34 = Week 34, 35 = Week 35, I manually change this every week.
Sheet2 is my summary page, I need to input my formula in this sheet. In this sheet I have the word "Subtotal" in A3 (This is what I'm going to match.
Sheet3 has my data input.
In sheet 3 what remains constant is row 1 which has my YEARWEEK numbers e.g 201601, 201602.. so I would need a look up to Sheet1 C3 matching to A1:AA1 RIGHT(Cell,2). Also in column A the word "Subtotal" remains constant but the end of the string changes so I would need a LEFT(Cell,8).
The INDEX
So the whole INDEX MATCH MATCH is dynamic that when I change the week number in Sheet1, the value in Sheet2 G3 will update with the Subtotal in Sheet3.
Is this possible?
Small sample data without different worksheets, I can add these in when inputting the formula.
<colgroup><col><col><col span="3"><col><col><col><col span="3"></colgroup><tbody>
</tbody>
So if I was to change the 34 to 35, the subtotal would change to 257.
But also the Depts get bigger each data input.
Thanks,
I think quite tricky situation and I may be going about it the wrong way.
I have 3 sheets.
Sheet1 has a value in C3 which is my week number, 34 = Week 34, 35 = Week 35, I manually change this every week.
Sheet2 is my summary page, I need to input my formula in this sheet. In this sheet I have the word "Subtotal" in A3 (This is what I'm going to match.
Sheet3 has my data input.
In sheet 3 what remains constant is row 1 which has my YEARWEEK numbers e.g 201601, 201602.. so I would need a look up to Sheet1 C3 matching to A1:AA1 RIGHT(Cell,2). Also in column A the word "Subtotal" remains constant but the end of the string changes so I would need a LEFT(Cell,8).
The INDEX
So the whole INDEX MATCH MATCH is dynamic that when I change the week number in Sheet1, the value in Sheet2 G3 will update with the Subtotal in Sheet3.
Is this possible?
Small sample data without different worksheets, I can add these in when inputting the formula.
201633 | 201634 | 201635 | ||||||||
34 | Subtotal | 207 | Brand | Dept | Dept Name | |||||
Dept1 | Name1 | 19 | 78 | 26 | ||||||
Dept2 | Name2 | 83 | 6 | 81 | ||||||
Dept3 | Name3 | 84 | 86 | 67 | ||||||
Dept4 | Name4 | 81 | 19 | 15 | ||||||
Dept5 | Name5 | 2 | 18 | 68 | ||||||
Subtotal: 0001 | 269 | 207 | 257 |
<colgroup><col><col><col span="3"><col><col><col><col span="3"></colgroup><tbody>
</tbody>
So if I was to change the 34 to 35, the subtotal would change to 257.
But also the Depts get bigger each data input.
Thanks,