Help Automatically Populating A Summary sheet

[Box666]

Help Automatically Populating A Summary sheet


I have 2 worksheets,:-

On sheet1 I have a series of columns going across the page (left to
right) each relating to one month. The rows going down the page relate
to the items sold that month. Each month a new column will be added
(relating to the month that has just ended) so on the 1st of March I
will add a new column relating to Februarys sales.


Sheet 2 is a brief summary of sheet1 and comparison of the last
6months sales. I would like sheet2 to update automatically as soon as
new data is added to sheet1. I would like it to detect the right hand
column of sheet 1 ie the last data added and display it on sheet 2.
Further I want it to total the last 6months data (the last 6 columns
on the right of sheet1)


Could somebody advise me how I can achieve this please?

 

[Guest]

Assuming your months are from column B to M and data starts in row 2
, then this will total the last 6 months - place in N2 (?):

=SUM(OFFSET($B2,0,MAX(0,COUNTA($B2:$M2)-6),1,COUNTA($B2:$M2)))

Copy down

Regarding updating Sheet2, perhaps something like:


=IF(Sheet1!B2<>"",Sheet1!B2,"") and copy across and down


I don't understand how sheet2 is a BRIEF Summary if your copying (all?) the
data from Sheet1.

HTH