Any Excel masters around that can help me?

[Oryan77]

I have seen excel sheets that have pull down menus in the spreadsheet...or at least I think I have seen it done.

I have a spreadsheet that I would like to hide groups of rows & columns but have a tab visible on a row so I can click it and make the hidden rows/columns appear, or click the tab and make the rows/columns hidden. I would also like to be able to name the tab so I know what content it controls.

Is this possible? If so, how can I do it? I have looked online but can't seem to find any info about it.

 

[Croesus]

The only way I've found to hide/reveal columns is create a couple macros, add a couple buttons to the worksheet, and attach the appropriate macro to each button.

I've attached a file that I put together a while back. It's an ice breaker for team meetings. Since everyone wants to know the answers after it's over, I setup the worksheet to start with the answers hidden, and a couple buttons to reveal/hide the answers.

As for pull down menus, use Data - Validation, and select List. That will allow you to define a list of values somewhere else on the worksheet (the list can be hidden after you define your drop down). Earlier versions of Excel (thru Excel 2002) will not allow you to point to a list on a different worksheet within the same spreadsheet file, but the latest version might.

Hope this helps.

 

[Oryan77]

Oh just realized I used the wrong terminology. I didn't mean a "pulldown menu". I actually don't know what you would call it. But your sheet is exactly what I was talking about. A "hide/reveal" button.

Would you mind explaining how you created that button? Or do you know what it is called so I can look it up in the Excel help menu and see how to create one?

I'd like to make what you have, except have it hide certain rows & columns. For example, hide rows A1-A50 along with B1-B50.

Also, is it possible to have 1 button perform both the hide & reveal action rather than having 1 button for each action? Click it once to reveal, click it again to hide.

 

[Brentos]

Instead of hiding the data, you could use the button to have the format change to white text on white background, thus making it invisible to the eye (it would still print, thought). Then when you prese the button again, it becomes black on white. Would that help? I could walk you through that.

 

[Oryan77]

Would that help?

Well not exactly. The reason I want to hide the rows is so it collapses everything and I don't have to scroll down to view certain info as much. I'd like to see a list of hidden tabs and be able to click on a tab to reveal it's information.

Basically, I want it to do exactly like this...

[sblock]Information I want to see, but hide it when I'm done reading it.[/sblock]

 

[Brentos]

Gotcha. You can use grouping and outlines to collapse entire rows or columns. You have +'s on the side that open and close the rows. See attached. Is that closer?

 

[Oryan77]

Gotcha. You can use grouping and outlines to collapse entire rows or columns. You have +'s on the side that open and close the rows. See attached. Is that closer?

That could work just fine if I'm able to have a little more control in creating it.

1. For example, the + button appears at the last row when revealed...are you able to reverse it so the + button stays at the top row and the hidden rows are revealed below it? That would keep me from scrolling down just to hide the rows again (I will have quite a few rows grouped to hide.

2. You can have multiple buttons like that for different groups of rows right?

3. Can you have the + button inside another + button? I would probably end up needing to hide a group of rows with a larger group of rows. Sort of like a tree.

But that's a start at least. I could actually use what you showed me even if it's not perfect, thanks!

 

[maransreth]

That could work just fine if I'm able to have a little more control in creating it.

1. For example, the + button appears at the last row when revealed...are you able to reverse it so the + button stays at the top row and the hidden rows are revealed below it? That would keep me from scrolling down just to hide the rows again (I will have quite a few rows grouped to hide.

The location of the + sign is determined by Excel, unfortunately you cannot move it.

3. Can you have the + button inside another + button? I would probably end up needing to hide a group of rows with a larger group of rows. Sort of like a tree.

You can have as many grouping levels as you would like. Start with the first level and make those groups, then while those rows are visible, make then next group, etc.

 

[Ayrk]

If you are using Excel 2007, you can create custom views and then make a macro to switch between them.

Go to the View tab and click Custom Views. Click Add and name the first one Standard or something. Then hide all the rows and columns you want, adjust zoom, and whatever you want. Click on Custom View and add that one with a new name. Keep doing that until you have all your possible views. Then record a macro switching between each one. You'll probably have to have a separate button for each view though.

 

[Brentos]

Yes to 2. and 3. Let me check on 1. and get back to you...but I don't think so.

One last thought, how about hyperlinks and have different tabs with the full answers and without...that might have the same affect? (I'll try to post an example later).