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 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.