If you have a multi-level list field in your system, such as the following and want to split a report up to show a different column in your report for each available level, this article explains how to do this.
Example of a multi-level (cascading) list:
- Top Level 1 | Option A | Sub-category i
- Top Level 2 | Option B | Sub-category ii
Although strictly possible using various standard Excel formula, it's just not very easy and unfortunately, the system doesn't currently do this for you (yet). Excel doesn't have a standard formula which "just does this" nice and easy, so we've created a custom function that simplifies this. We've called our function "GetSplit".
To use our custom function, please first download the Excel file attached to this article and open it and make sure you enable macros when you open the Excel file (download link is at the end of this article).
In the Excel file, on Sheet1, you'll see an example of a field and data which has a multi-level list in column A, then in columns B-D, example usage of the custom "GetSplit" function.
The use of the function is defined as follows:
Formula: =GetSplit(a2," | ",1)
Where:
- a2 is the column/row reference in row 2 where your original data is
- " | " is whatever character is being used as the data separator, in this case the pipe character (|)
- 1 is the level of the list to be returned, so you just need to change this to be 2, 3, etc., depending on which level you want to display in the column.
So, now all you need to do is to get your raw data into our ready-made Excel sheet. We'd recommend live-linking data from the system to do this, so you only ever have to enter the additional "GetSplit" columns once and can then just refresh your report from within Excel at any time in the future to update the data.
Click here for information on how to create a Live-linked report.
Once you've got your raw data linked to Excel, you'll also want to use the "Fill down formulas in columns adjacent to data" option for your additional "GetSplit" columns.
To do this, just right-click anywhere in your linked raw data and choose "Data range properties". Then select the option for "Fill down formulas in columns adjacent to data", which is at the bottom of the pop-up box.
This just means you can add additional calculated columns to the right of your linked data. Simply add as many "GetSplit" columns as you need in row 2 to the right of the linked data with appropriate column headings for each new column. When you're done, do a Right click > Refresh in the linked data to update everything, including your new calculated column(s).
Technical detail
(Non-Techie folk or people who aren't competent Excel users may want to look away now...)
If you are really interested in how this works or want to add this function to a workbook you've already created, feel free to download the .txt file attached to this article, which gives the full Visual Basic (VB) code of the function.
This function needs to be added to a module in your workbook. If you are not particularly familiar with Excel, the easiest way to do this is as follows:
- Go View > Macros > Record Macro, click OK and then immediately click the "Stop" icon to stop recording (e.g. the stop icon appears bottom left next to the "Ready" message in Excel 2013).
- If you have no macros at all in your workbook, this will most likely create a module called "Module 1" in your workbook with a macro called Macro1 in that Module.
- You can then go View > Macros > View Macros > Select "Macro1" > Edit
- Replace all the VB code for "Macro1" with the VB code of the custom GetSplit function.