Rolling 6 Month Stats

[nberryman]

Is it possible to get Excel to produce a figure from different sheets using the sheet name as a rolling criteria?

I have 12 sheets April to March with a value in the same cell on each sheet. I want to calculate the value for the last 6 months so in October I want the total from Sheets April - Sep, in November I want May to October and so on.

Any halp really appreciated (again)

Regards

Neil

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[SkipVought]

Neil,

The horse is out of the barn,

but it is very bad practice to put similar database/table stuff on separate sheets. If your table of data had a DATE column and it were all in one table, getting the answer to your question would be a piece of cake!

My suggestion is to cut 'n' paste it all into one sheet (adding a DATE column) and then you could use...

PivotTables
Filters
Subtotal Wizard
Lookup functions
Database functions

and a plethora of other tools available to data analysis in tables


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[nberryman]

Yeh, I agree but I have inherited the workbook and the "Boss" want's to retain the look so users don't get confused.

Cheers

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[Loomah]

OK
Possible middle ground would be to create a summary sheet of all the data that is required - maybe totals from the individual sheets - then work from that using pivot tables, Dfunctions etc etc as Skip suggested.

Or you could spend time, the way I just have, ignoring all those things and working on a pain in the rear formula!!!!
But you'd still need to summarise the data somehow.

Happy Friday
Just off to clog the roads up on a school run!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SkipVought]

I suggest then that you write a routine to accumulate all the sheets into a single table.

Could start out like this

Create a CompositeTable sheet with headings exactly like each sheet headings with the exception of Month

I am assuming that your month sheets are named by month and that the data headings are in row 1 and that ALL data is contiguous.

Dim wsComp As Worksheet
Set wsComp = Worksheets("CompositeTable")
For Each ws In Worksheets
  With ws
    Select Case .Name
    Case "Jan", "Feb", "Mar" 'etc
      rOut = wsComp.Cells(1, 1).Rows.Count + 1
      With .Cells(1, 1).CurrentRegion
        Range(.Cells(2, 1), Cells(.Rows.Count, .Columns.Count)).Copy _
         Destination:=wsComp.Cells(rOut, 1)
      End With
      With wsComp
        Range(.Cells(rOut, .Columns.Count), .Cells(.Rows.Count, .Columns.Count)).Value = .Name
      End With
    End Select
  End With
Next

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DaleWatson123321]

Neil,

From your description, if it's no more complex than you describe, it should be a simple matter of assigning range names to each of the totals in the different sheets.

Then on a Summary sheet, reference the named cells via formula.

I've gone ahead and developed an example file for you, where I've added a little extra - a vlookup formula that pulls in the total of the last six months, based on the current month.

If you'd like the file, email me and I'll send it.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[PepeLeMokko]

Also look at the possibility of making a Pivot Table using the multiple consolidation ranges option in the Pivot Table wizard.

 

[nberryman]

Creating a new sheet to collect the values from the other sheets and Vlookup did the trick.

Thanks for the guidance



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[murad5]

Or (assuming the sheets are arranged sequentially, have the names of the months and the data you want to sum is in cell A1) you could use "=sum(April:September!A1)"