K. Georgiadis
05-17-2005, 06:55 PM
I can't figure out how to solve the following problem using Excel's built in functions, so I am hoping that there is a VBA solution.
Attached is a zip file illustrating the challenge. This is intended to be a template that can accommodate up to 30 market segments:
1. In the worksheet named "Segment Data" there will be 30 segments, each with 3 sales scenarios. One of the 3 scenarios will be selected with a combo box and will be displayed on the "Active Scenario" row. The Active Scenario row will be appearing at 7-row intervals, as shown in the attached example
2. In the majority of cases, half (or fewer) of the segment blocks will be used but, because the "Active Scenario" row uses nested IF formulas, the row will not be blank but will contain zeros even if the data block is not used. For now, I am using the logic that if the sum total of all three scenarios for the planning period is zero, than it is safe to assume that the segment data block was not used.
3. The Active Rows are captured contiguously in "Sales Summary" and each column is aggregated using the SUM function.
The objective is to hide the unused rows, so as to make the table neat and compact, but to make them reappear automatically if the user later decides to populate a previously unused segment block.
Any suggestions?
THANX!!!!
Attached is a zip file illustrating the challenge. This is intended to be a template that can accommodate up to 30 market segments:
1. In the worksheet named "Segment Data" there will be 30 segments, each with 3 sales scenarios. One of the 3 scenarios will be selected with a combo box and will be displayed on the "Active Scenario" row. The Active Scenario row will be appearing at 7-row intervals, as shown in the attached example
2. In the majority of cases, half (or fewer) of the segment blocks will be used but, because the "Active Scenario" row uses nested IF formulas, the row will not be blank but will contain zeros even if the data block is not used. For now, I am using the logic that if the sum total of all three scenarios for the planning period is zero, than it is safe to assume that the segment data block was not used.
3. The Active Rows are captured contiguously in "Sales Summary" and each column is aggregated using the SUM function.
The objective is to hide the unused rows, so as to make the table neat and compact, but to make them reappear automatically if the user later decides to populate a previously unused segment block.
Any suggestions?
THANX!!!!