PDA

View Full Version : Solved: How to Make Worksheet Expand and/or Collapse Rows



RonMcK
10-04-2010, 03:02 PM
Hi, All,

I have a list with Units in a book, Lessons within each Unit, and Pages within each Lesson. I would like to produce a worksheet that would allow me to expand or collapse units, or lessons within units so I can hide detail when it is not needed. And, I need to create an option to fully expand or collapse the worksheet. The accompanying worksheet shows a mock-up of what I would like to be able to do. I probably need to be able to print the worksheet in any particular 'state'.

Is the easiest solution using VBA code to hide/unhide rows using, perhaps using data in hidden columns to facilitate the process?

Thanks!

Bob Phillips
10-04-2010, 03:42 PM
Why not just use the built-in Group/Ungroup functions?

RonMcK
10-05-2010, 11:23 AM
Bob,

Yes, why not? 'Cause I don't know my Excel well enough? Do you offer a remedial Excel class? I wonder what else has snuck into Excel while I wasn't paying attention? A BIG Thank you for the point out.

Okay, I'm trying to use Group/Ungroup on the worksheet I posted. It appears to me, when one has nested levels, that one needs to start building groups from outermost level and work inward.

Here is my worksheet. The problem I'm encountering is that sometimes Excel shows that it's grouping a range but it fails to display a bracket and +/- sign. In this case, there appears to be no way to collapse Lesson 2 of Unit 1. When I highlighted rows 13-20 and clicked Group, the dot for each row moved over as expected (down a level) however Excel did not add the bracket and - sign like it did when I grouped rows 6-11 above, or as it did with the two lessons in unit 2, below.

It's done this each of the three times I tried to "group" this lesson. When I was "ungrouping" my first attempt on the unit (working from inner most to outer most), I noticed that after I ungrouped my first grouping (rows 5 to 20) suddenly a bracket and - sign appeared on rows 13-20. This wasn't replicated the second time or third times I attempted to group this unit and its lessons.

Suggestions?

RonMcK
10-05-2010, 12:08 PM
Bob,

For some reason, I need to complete each intermediate grouping with a blank row. Apparently within a grouping, subgroupings must be separated by a 'non-grouped' line else Excel becomes confused. For Lesson 1 in each Unit the Lesson 2 header takes care of this but after each Lesson 2, I need a blank line.

In my first workbook, the last line of unit 2 lesson 2 was not getting rolled up. Including a blank line after the last grouping in each subgroup seems do the trick.

See the accompanying updated copy of the workbook.

Thanks,

Bob Phillips
10-05-2010, 12:19 PM
That was what I was going to suggest Ron, adding a blank row to each outer group. Excel tries to be smart, too smart for its own good sometimes.

RonMcK
10-05-2010, 12:21 PM
Can I mix grouped rows and columns in the same worksheet?

The "wow" factor on what I'm building is increasing exponentially.

;-)

Thanks,

Bob Phillips
10-05-2010, 12:38 PM
Yeah, of course you can. Excel is 2D.

RonMcK
10-05-2010, 12:42 PM
Wahoo! Thanks, Bob!

Have a pleasant evening.

RonMcK
10-13-2010, 11:30 AM
On the Excel 2007 ribbon, I find a Subtotal button in the "outline" group. This lets me "[t]otal several rows of related data together by automatically inserting subtotals and totals for the selected cells."

Apparently this only allows one level of subtotals and collapsible groups. Is there anyway, using this tool, to generate nested levels of subtotals?

Or do I have to manually inserts rows and formulas for the intermediate information that I need.

Thanks,

Bob Phillips
10-15-2010, 09:27 AM
You should be able to do it by adding subtotals twice, once at the outer level, once at the inner level. Just make sure that you uncheck the box to replace existing totals on the secnd iteration.

RonMcK
10-15-2010, 10:50 AM
Bob,

Okay, I think I have a handle on the process; I worked from the outer most value (Grade) to the inner most (Lesson), counting pages. See the attached worksheet.

I also need to present information on the number of lessons within units and grades, and units within grades, and grand totals of those two for all grades.

Can I use the "subtotal" function on the Outline group to do this? When I asked XL to "At each change in:" Unit <count> "Add subtotal to:" Unit, XL counted lines (so Lessons) in the Unit not the number of units.

Am I misinterpreting how I get this critter to cooperate with me?


Thanks,