Details from 52 worksheets into one master sheet Help please

  • Thread starter Thread starter Derek Peters
  • Start date Start date
D

Derek Peters

Hi Gord Dibben has given me this formula to take the details from 52 weekly
sheets and give me a total sheet for the year and it works very well (Thanks
Gord)


If sheets are not named Week1 through Week2, you can insert a dummy sheet to
the right of Totals sheet. Name it Start.

Add another dummy sheet after last sheet. Name it End.

Formula in Totals =Sum(Start:End!G25)

Second method allows you to insert new sheets between Start and End sheets
as
your weeks progress.

Gord Dibben Excel MVP



What I would now like to do is make one sheet and have all the weekly totals
listed so I can look at it and see all the sales ,fuel and so on.


There are about 20 entries per week and I can link from a new sheet back to
Week 1 and get the data I need but do I need to do this 52 times (I E 20
links to week 1 and the same for the other 51 weeks) or can this data be
moved in a better way.

Many thanks for your help with this

Regards
Derek
 
One approach is to set-up your "Start" sheet with the names of all your
weekly sheets in a column.
Across the rows enter labels for the 20 categories that your interested in
reviewing.

For example, starting in A2 and going down,
Sheet1
Sheet2
Sheet3
.....
Sheet52

Now, say the first cell of interest on *all* the sheets would be C20.
Enter this in B2:

=INDIRECT(A2&"!C20")

Drag down to copy, and you now have all the data from C20 in all 52 sheets.

Whether or not you can drag this formula *across* the columns depends on
exactly which cells on the other 52 sheets you are looking to be returned.

Needless to say, the spelling of the sheets in column A must match exactly
the actual sheet names in the WB.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================




Hi Gord Dibben has given me this formula to take the details from 52 weekly
sheets and give me a total sheet for the year and it works very well (Thanks
Gord)


If sheets are not named Week1 through Week2, you can insert a dummy sheet to
the right of Totals sheet. Name it Start.

Add another dummy sheet after last sheet. Name it End.

Formula in Totals =Sum(Start:End!G25)

Second method allows you to insert new sheets between Start and End sheets
as
your weeks progress.

Gord Dibben Excel MVP



What I would now like to do is make one sheet and have all the weekly totals
listed so I can look at it and see all the sales ,fuel and so on.


There are about 20 entries per week and I can link from a new sheet back to
Week 1 and get the data I need but do I need to do this 52 times (I E 20
links to week 1 and the same for the other 51 weeks) or can this data be
moved in a better way.

Many thanks for your help with this

Regards
Derek
 
To get all sheetnames into column A of a new sheet......

''list of sheet names in a workbook - placed on a new worksheet
Sub ShowNames()
Set wkbkToCount = ActiveWorkbook
iRow = 1
With Sheets.Add
For Each ws In wkbkToCount.Worksheets
.Rows(iRow).Cells(1).Value = ws.Name
iRow = iRow + 1
Next
End With
End Sub

Saves a lot of typing.

Gord Dibben Excel MVP
 
Back
Top