Hi


Tip: from Dave Peterson

I'd create two new worksheets--one to the far right and one to the far left. Call them Start and End
Then using a sheet (Summary) that is outside this "sandwich" of worksheets with this formula:
=Sum(Start:End!A1)

Then you can drag sheets in and out of that sandwich to play what if games.
I'd put a couple of notes on each of these sheets: "don't delete this sheet!"
And protect the worksheets so that people don't use it for real data.


See also
http://www.rondebruin.nl/linksum.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message ...
I've got a stack of code to generate reports for sales advisers. One
workbook containing one worksheet per sales adviser.

I've also got code to bulk up these reports into teams, so one workbook per
team, with one worksheet per adviser (worksheet name = adviser name).

What I want to do is put in a summary (or manager) sheet on the team workbook.

As all adviser sheets are structurally the same, it's just a case of making
formulas which look at the same point on each sheet, so something like
=SUM(First Sheet:Last Sheet!H:H)

Thing is, I don't know how many sheets there will be, or what they'll be
called.

I've wondered about doing it as follows:

Define a variable to hold the first sheet name, likewise the last sheet
name, then bung in the manager sheet and set the formulas to the variable
values.

Thing is, how do I get the first and last sheet names into variables? (Esp.
the last - thet'll be the difficult one)

Any help you can give would be great.

Cheers,

Tom.