Excel Marcos - adding columns and changing SUM formulas

Soldato
Joined
7 Nov 2005
Posts
4,955
Location
Widnes
Hi guys,

I have a sheet that takes a list of totals and asks the user to break down the totals into groups and descriptions.

For example:

Column B = Total to break down
Column C = Reconciliation showing amount missing from all columns to get back to match Column B.
Column D to E = Different employee names in Team A.
Column F = Sub-total for Team A.
Column G to L = Different employee names in Team B.
Column M = Sub-total for Team B.

Is there an easy way to allow the user to add their own columns which will also update the sub-total formula and update the reconciliation formula? If they manually add columns it doesn't always update the sub-total (depending where you add the column). They also need to add new teams, which will need to be reflected in the reconciliation formula. They may also need to be able to delete employees/teams.

I assumed Macros are the answer but I've never used them. If Excel has some functionality that gets me most of the way there without Macros -> bonus!
 
Soldato
Joined
25 Jun 2011
Posts
5,468
Location
Yorkshire and proud of it!
Hi guys,

I have a sheet that takes a list of totals and asks the user to break down the totals into groups and descriptions.

For example:

Column B = Total to break down
Column C = Reconciliation showing amount missing from all columns to get back to match Column B.
Column D to E = Different employee names in Team A.
Column F = Sub-total for Team A.
Column G to L = Different employee names in Team B.
Column M = Sub-total for Team B.

Is there an easy way to allow the user to add their own columns which will also update the sub-total formula and update the reconciliation formula? If they manually add columns it doesn't always update the sub-total (depending where you add the column). They also need to add new teams, which will need to be reflected in the reconciliation formula. They may also need to be able to delete employees/teams.

I assumed Macros are the answer but I've never used them. If Excel has some functionality that gets me most of the way there without Macros -> bonus!

Let me repeat back to you and tell me if I've understood correctly. It's a little unclear to me.

You have columns containing totals and in between these columns are other columns which relate to the column at the end and then this pattern repeats for the next set of columns, and so on. Or maybe it's the first in the column in the sequence that is the total. But either way it's this iterating pattern of columns, like Xa, Xb, Xc... XTotal. And then Ya, Yb, Yc... YTotal. Something like that? And the problem is you want different users to be able to add extra columns in each group according to whatever is specific to them and for XTotal to be updated correctly, etc.

Have I understood the pattern you're using here? If so, it ought to work as written so long as XTotal is in the format of SUM(xa,xc) or whatever. Probably users are breaking it by adding columns at the ends. E.g. if your XTotal cell is something like =SUM(A5 : D5) and they add a column after D, it will be missed out. Whereas if they added one in between B and C, the formula would be updated. But I wont say any more because I'm not convinced I've understood your scenario correctly.

Have you considered giving people a multi-worksheet Excel file with a worksheet for each team? Then they can just work in their own tab and not break anything. You can have a Summary worksheet that imports totals or whatever you want from each of those sheets in a range.
 
Back
Top Bottom