Copy two summary ranges to master summary sheet

[a m spock]

I have a workbook with an ever increasing number of worksheets - one for
each sales person with a standard layout. The data on each sheet gets
summarised in specified ranges of that sheet like a1:a5 for sales of
different and g9:g13 for value of sales etc. I need a summary sheet where
for each sales person there is one row showing his name (same as worksheet
name) and only these cells.

 

[Don Guillett]

Why not just have the master sheet with a column for sales person name and
then filter when desired.

 

[WhytheQ]

Why not use formulas in the summary sheet (rather than vba)
You can use the INDIRECT function e.g if you have a sheet named
MySheetName:

in cell A1 of the summary type 'MySheetName'
in cell A2 of the summary sheet, type the formula =INDIRECT(A1 &

"A1")
This should return the value in A1 of the sheet 'MySheetName'.
Can you see how this can be applied to any new template sheets added
to the workbook, so that the data will be pulled through to the
Summary sheet?

Hope this helps,
Jason.

 

[a m spock]

the dat i need to pull into the summary sheet is itself a summary of the data

 

[a m spock]

sounds good. but sorry. can't seem to make it work. let us say the sheet name
is "mysheet1" and data to be pulled from the sheet is name ranges "mysales"
and "mysalesvalue" in each sheet. there are fifty such sheets "mysheet1" to
"mysheet50". i create a coulm in the summary sheet with the sheetnames in
column A. what next?

 

[WhytheQ]

in this case not so much need for named ranges.
As an example imagine all the numbers you wish to pull through to the
summary are in A1 of every sheet and the sheets are named Sheet3 to
Sheet8, then the following can be used:

ColA ColB ColC
Sheet3 A1 =INDIRECT(B5 & "!" & C5)
Sheet4 A1 =INDIRECT(B5 & "!" & C5)
Sheet5 A1 =INDIRECT(B5 & "!" & C5)
Sheet6 A1 =INDIRECT(B5 & "!" & C5)
Sheet7 A1 =INDIRECT(B5 & "!" & C5)
Sheet8 A1 =INDIRECT(B5 & "!" & C5)

Try entering numbers in A1 of any of the sheets - they should get
pulled though to the summary.
J