• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multi work sheet workbook

A

Arian Droogendyk

New Member
Good day, all:

I am developing a work book for estimating for one of my clients, and they want me to add in the ability to enter actual performance information, as well as the creation of a variance report.

I believe that the best way to do this is to create multiple worksheets all within the same work book (which I'm fine with). My question is whether I can group some of the worksheets under a common tab at the bottom, so that only some of them show up at a time.

EG: The user could click on "Estimating" and three worksheets would be available for review, or he could click on "Actual" and three other worksheets would be available for review, but they would not have to have all 6 available at any one time. Recognize that I am dealing with significantly more than 3 for each area, but the question doesn't change.

Thank you,

Arian Droogendyk
KAD Consulting
 
Short answer, you cannot group worksheets as you describe.

Alternative:
a) Setup a sort of Main Page/Table of Contents with various buttons. Buttons use macro to hide/unhide groups of sheets.
b) Instead of multiple sheets use multiple rows within the same sheet. XL 2007+ has over a million, so you would have enough space probably. You can then use the Data - Grouping feating to group rows and/or columns into sets. I commonly see this in financial type reports, where items tend to "roll-up" into other things (So, first dirll down might show countries, next shows states, next shows cities, etc.)
 
Ok.

After some attempts at it, I think I could make option 2 work, but it would be a bit unwieldy.

I have never written macro's, but that sounds like a much cleaner approach to the issue. How would I look up what the macro should look like to make that work?


Arian Droogendyk
KAD Consulting
 
The most basic would be to record a macro of you hiding/unhiding the worksheets in question. Overall, the code won't be too complicated. To unhide, will be 1 line per sheet name. For hiding the sheets, you can select a group of sheets, and hide them all at once, which means you only need 1 line of code.
 
You must log in or register to reply here.
Back
Top