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.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"a m spock" <(E-Mail Removed)> wrote in message
news:BD3B81C0-4F07-4779-B062-(E-Mail Removed)...
>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.

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.



On 11 Sep, 15:21, a m spock <amsp...@discussions.microsoft.com> wrote:
> 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.

the dat i need to pull into the summary sheet is itself a summary of the data
on each worksheet. the worksheet itself is used for various other purposes.

"Don Guillett" wrote:

> Why not just have the master sheet with a column for sales person name and
> then filter when desired.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "a m spock" <(E-Mail Removed)> wrote in message
> news:BD3B81C0-4F07-4779-B062-(E-Mail Removed)...
> >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.
>
>

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" wrote:

> 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.
>
>
>
> On 11 Sep, 15:21, a m spock <amsp...@discussions.microsoft.com> wrote:
> > 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.
>
>

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




On Sep 11, 5:03*pm, a m spock <amsp...@discussions.microsoft.com>
wrote:
> 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" wrote:
> > 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.
>
> > On 11 Sep, 15:21, a m spock <amsp...@discussions.microsoft.com> wrote:
> > > 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.- Hide quoted text -
>
> - Show quoted text -