too many worksheets in workbook

  • Thread starter Thread starter JerryK
  • Start date Start date
J

JerryK

I have a user that has a lot of worksheets in one workbook, and wants to add
more. It is beginning to get cumbersome to scroll through the worksheets to
get to the correct one. I know this is a vague question, but does anyone
have suggestions on how to reorganize the spreadsheet?

One idea I had was to provide macros to show one grouping of worksheets
while hiding another grouping. For example, macroS would show worksheets S1
and S2 while hiding P1 and P2; macroP would show worksheets P1 and P2 while
hiding S1 and S2.

They are using Excel 2002.

Thank you.
Jerry
 
Hi

You can right-click on tabs scrolling buttons left to sheet tabs tabs, and
select wanted sheet from drop-down menu.

But in general - a huge number of sheets indicates bad design.
1. When there are lot of sheets used for data input, it'll be dificcult to
remember the right sheet you need at moment. And when there are sheets with
identical layout, then there always exists a risk to enter data into wrong
sheet accidentally. And when yo need to consolidate data from many sheets,
you probably face huge formulas, which are difficult to edit, and are
slowing down your workbook.
2. When there are lot of sheets, which read data from other (input) sheets,
then mostly you can replace them with a limited number of dynamic report
sheets, with 1 - 2 dropdown lists do determine data filtering conditions.
 
It's not the solution that'd work best in the long run, but you could add
yet another sheet as an index, with hyperlinks to each sheet, and hyperlinks
on each sheet to return to the index.
 
You could put prefixes in the names of each worksheet and then sort the
worksheets whenever you needed:

Chip Pearson's:
http://www.cpearson.com/excel/sortws.htm

David McRitchie's:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sortallsheets

If you like the idea of using an table of contents worksheet (like Beege)
suggested, you can find some code also at David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

And if you wanted a floating toolbar that can show you the names of worksheets
to select:
http://groups.google.com/[email protected]

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And maybe you could reorganize your data and keep more data on "combined"
worksheets. If you include some kind of indicator that you use to separate into
different worksheets (state/year sold/customer name, something????), maybe just
add a column for that indicator and use Data|Filter|autofilter.

You can choose what to look at and what to hide.
 
Back
Top