Finding First and Last Worksheet Names

B

ben_hur

I currently have a workbook of around 25 worksheets, with 1 summary
worksheet. The 25 worksheets are of identical format but have
different names (sorted alphabetically).

On the summary page I have used functions such as SUM(BBB:YYY!$B$1) to
get total values. However I am currently adding 3-4 extra worksheets a
month and I would like these to be automatically calculated in the
formula (i.e. if I add sheet AAA or ZZZ, I will have to manually adjust
the formula).

Is there a formula that shows the name of the first and last worksheets
so that I can add an INDIRECT command to my formula and have it
automatically update whenever I add a sheet? Or is there another way
of approaching this? Ideally I'd like to avoid using a macro as this
will be sent out to dozens of people, many of whom probably have their
security settings on high.

Thanks,
BH
 
G

Guest

Hi
You could put a sheet at the end of your book, called Last (or something)
and use
=SUM(BBB:Last!$B$1)
You can also hide the Last sheet, if you like, so it doesn't confuse your
users.
 
P

Peter Rooney

Why don't you add dummy empty worksheets at the beginning and between the
last sheet to be summarised and the summary sheet of your workbook, and
reference these in your SUM formulae. As long as you don't add new worksheets
before the first dummy or after the last one, you should be fine.

Not the neatest, but, if you don't want code, it should work, providing your
users are told not to add new sheets before the first dummy or after the last
one.

Have a good weekend

Pete
 
J

Jason Morin

The trick is to insert 2 dummy sheets, one in the beginning and one at the
end. Name them "beg" and "end" and hide both sheets. Now use:

=SUM(beg:end!$B$1)

Any sheets you insert, regardless of name and location, will be included in
the formula.

HTH
Jason
Atlanta, GA
 
P

Peter Rooney

I thought about hiding them, but if you do, there's still always the chance
that you'll insert a new sheet between your summary sheet and your end dummy.
Better to leave them visible and tell people not to add sheets before/after
the first/last one.

Pete
 
Top