Probably vey easy question

[Cheenix]

I have 3 seperate spreadsheets for the week, Month and Year.
They all have colums and rows for inputing data for each branch and I
want to update the Month and year ones when I input the data on the
week one and to keep the totals in month and year when I update each
branch on a weekly basis.
I am presuming this is easy but can not get the formaula to work.
Sorry if this dosent make much sense but just started using excel and
have been asked to do this for work.
Any help would be appreciated.

Thanks
Cheenix

 

[Don Guillett]

A better way to do this is to have only ONE and use filters or sumproduct
formulas to show the month & year totals. No reason to have 3 sheets. To sum
col b for month 1 (Jan) use this
=sumproduct((month(a2:a22)=1)*b2:b22)
or
=sumproduct(--(month(a2:a22)=1),b2:b22)

 

[Cheenix]

A better way to do this is to have only ONE and use filters or sumproduct
formulas to show the month & year totals. No reason to have 3 sheets. To sum
col b for month 1 (Jan) use this
=sumproduct((month(a2:a22)=1)*b2:b22)
or
=sumproduct(--(month(a2:a22)=1),b2:b22)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software






- Show quoted text -

Thanks Don,
But that has me totally lost as a new user.

I have been asked to show 3 seperate tables from the boss so no
movement on that.

Any further help would be great or I can mail a copy of the sheet to
you if that makes it easier to understand me.

Thanks

 

[Don Guillett]

Go ahead and mail to my address below along with a snippet of this on an
inserted sheet.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

A better way to do this is to have only ONE and use filters or sumproduct
formulas to show the month & year totals. No reason to have 3 sheets. To
sum
col b for month 1 (Jan) use this
=sumproduct((month(a2:a22)=1)*b2:b22)
or
=sumproduct(--(month(a2:a22)=1),b2:b22)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
message






- Show quoted text -

Thanks Don,
But that has me totally lost as a new user.

I have been asked to show 3 seperate tables from the boss so no
movement on that.

Any further help would be great or I can mail a copy of the sheet to
you if that makes it easier to understand me.

Thanks

 

[Don Guillett]

Gave OP this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 7 Or Target.Row > 15 Or Target.HasFormula Then Exit Sub
Target.Offset(34) = Target.Offset(34) + Target
Target.Offset(17) = Target.Offset(17) + Target
End Sub

 

[ShaneDevenshire]

Hi,

You could create a PivotTable from the weekly sheet and it would
automatically create subtotals for Weekly and Monthly data with just one
command.

Date Amt

Suppose your data contained a Date and an Amt column

1. Select all the data and choose Data, PivotTable & PivotChart Report,
click Next twice
2. Click the Layout button and drag the Date button on the right to the Row
area, drag the Amt button to the Data area,
3. Click OK, Finish
4. Put your cursor in the Date column of the pivot table and choose
PivotTable, Group and Show Detail, Group. Select Year and leave Month
selected. Click OK.

You should now see your weekly data with subtotals at the Monthly and Yearly
levels.