Expand / collapse option in excel

[vijehspaul]

Hi,
I have created a sample excel sheet (and attached) to explain my criteria.
In my case, there are test cases with some sub tests and test cases result will automatically populate based on the sub test result.
I would like to add expand / collapse options for the test cases with + / - signs.
Group feature is serving the purpose but i am looking little more.

1. Is there any way to make these expand/collapse options and give +/- signs in the sheets itself ? (instead of on the ruler) . NOTE : ▲ /▼ signs will also do.

2. I will be protecting the sheet with password with user can enter sub test result only. how to enable grouping on a protected sheet ?

Thanks in advance for your support.

 

[Hui]

Vijehspaul

a few pointers

1. In the data Validation C2:C11, you don't need to link it to a cell
You can simply use the words in the source separated by a comma


2. You can use a simpler formula in B2, B5 and B9:
=IF(COUNTIF(C2:C3,"Fail")=0,"Pass","Fail")
This saves using a helper column

3. In regards to your question you could add some shapes like:


Then use macro's to hide or unhide rows without grouping?

 

[Luke M]

Hi vijehspaul,

1. There's a way, but it would be complicated. you'd have to build all the shapes yourself, and then assign macros/VB to each one. IMO, it's way more hassle than it's worth. One small tip, the group function tends to put summary row at bottom by default (designed by accountants you put SUMs at bottom). If you click the small button in bottom right of the outline group


you can change that option

which I think lines things up better.


2. Sadly, MS did not give this option. You can't protect the sheet AND have an outline. Seems like a gross oversight to me, but it is what it is.

 

[Hui]

like this:


See attached file:

 

[Hui]

Luke

I used a single macro with the code
ActiveSheet.Shapes(Application.Caller).Name
which determines which shape called the macro

 

[Luke M]

Nice Hui. I keep forgetting about using Application.Caller. You're way certainly makes the process easier.

 

[Hui]

If the rows were setup evenly you could derive the row numbers from the Shape Names and simplify it even further to a single If then else function

 

[vijehspaul]

Thanks a lot Hui and Luke.