dirtywizard
New Member
- Joined
- May 1, 2015
- Messages
- 32
Hi
I have a workbook with three sheets
1) staging sheet full of floating column headers (changed as required by colleagues)
2) a sheet populated by a macro that aggregates the results from the staging sheet into a weekly breakdown
3) a summary sheet
In the summary sheet I have a table that uses VBA to get the column headers from sheet 2 and then is attempting to summarise the weekly figures. I've created a total summary successfully by using
=sum(index(weeklyoutput,,match(b8,weeklyheader,0))),0)
which matches the column headers in the summary sheet with the weekly sheet and pulls back the sum
(weeklyoutput is a dynamic range name covering the full table in sheet 2 and weeklyheaders are the headings in sheet 2)
I want to make another summary table that does the same thing but with a twist - static column C in sheet 2 is a channel view (e.g email, online displays etc)
sheet 3 (the summary tab) contains a channel list and I want to do a variant of the above formula to summarise by channel.
e.g. if on the summary tab column B (row 23) is "email", and there is a spend heading in column C, I want to get the summary of the spend for the email channel.
Because the column headers and orders can be changed on the fly in sheet 1 (and thus sheet 2) I can't do a standard sumif - I need to use something like the above index/match but bring in the condition of checking against column B.
Any ideas, as I'm at a loss...
Thanks in advance
I have a workbook with three sheets
1) staging sheet full of floating column headers (changed as required by colleagues)
2) a sheet populated by a macro that aggregates the results from the staging sheet into a weekly breakdown
3) a summary sheet
In the summary sheet I have a table that uses VBA to get the column headers from sheet 2 and then is attempting to summarise the weekly figures. I've created a total summary successfully by using
=sum(index(weeklyoutput,,match(b8,weeklyheader,0))),0)
which matches the column headers in the summary sheet with the weekly sheet and pulls back the sum
(weeklyoutput is a dynamic range name covering the full table in sheet 2 and weeklyheaders are the headings in sheet 2)
I want to make another summary table that does the same thing but with a twist - static column C in sheet 2 is a channel view (e.g email, online displays etc)
sheet 3 (the summary tab) contains a channel list and I want to do a variant of the above formula to summarise by channel.
e.g. if on the summary tab column B (row 23) is "email", and there is a spend heading in column C, I want to get the summary of the spend for the email channel.
Because the column headers and orders can be changed on the fly in sheet 1 (and thus sheet 2) I can't do a standard sumif - I need to use something like the above index/match but bring in the condition of checking against column B.
Any ideas, as I'm at a loss...
Thanks in advance