Rendering Workbooks with multiple Analysis grids in a sheet dynamic
Scenario: There is a requirement to create a workbook where multiple query outputs (analysis grids) are displayed in a single excel sheet and one below the other.
Issue: There is no control on the number of rows of these queries as for certain variable selections there may be just one row displayed and for other selections there may be multiple rows, so aligning the analysis grid one below another too close might lead to one query output overlapping the other and aligning the grids with lots of blank rows might lead to huge gaps between the query results.
Two query results overlapping:
Two query results with too many blank lines in between them:
Solution: To get the excel behave dynamically according to the number of rows and overcome the rigidity VB approach could be adopted, but this demands a sound knowledge in VB. The workbook can also be rendered dynamic without VB coding by just using an excel formula and custom views. This paper intends to explain the same.
Step 1: Create a workbook with Analysis Grids in a single sheet one below another. Ensure to maintain a gap of 50 to 100 rows from one grid to another so that there is sufficient room for the analysis grid to stretch.
Step2: To retain the actual blank rows which are necessary to maintain the spacing between two analysis grids or headers enter a formula in column “A” for these corresponding rows as =” ”.
Step 3: Key in a formula to identify the blank rows (based on the value in column A) on a different column, which will be hidden.
Step 4: Create a filter on the column with the formula. Filtering out the value “False” will provide all the rows which are non-blanks.
Step 5: Hide the column with the formula (ie., column Z in this example) and create Custom View from the View tab on the ribbon as highlighted.
Click on Add and name it as “Formatted”:
Step 6: Now un-hide the column with formula (column Z) and filter on “True” and hide it back again.
The output is formatted now with only blank rows:
Step 7: Hide the column with formula again and create a new custom view.
Step 8: Testing
Click on “Custom Views” under tab – “View”. Double click on “Formatted” and the workbook with the blank rows trimmed should show up:
Again click on “Custom Views” and choose “Unformatted” to return back to the initial view with blank rows.
Buttons could be inserted to assist the users with switching of the view and small macros can be recorded and assigned to these buttons, if done so the simple macro for each button appears as below: