Filtering and Name ranges in Modular worksheets

Hey Dave,

Yeah this forum is long overdue. We're officially launching it over the next couple of weeks and we're very excited about its potential to get more people involved in the community.

Regarding filtering within module components, we have not yet included this capability in the platform. We do have it on our list of improvements - along with other things we'd like to include in module component ranges like data tables and pivot tables - but we haven't got to it yet because it will require a very specific set of rules (because filterable rows will be different from category blocks in how they behave, etc.). So in the interim, we use freeform sheets to house data for filtering - see https://www.modano.com/resources/user_guide/freeform_content.

Regarding range names, you can select entire cell blocks within category blocks and name them. This is the recommended approach for lookup tables. However, you cannot insert a range name across multiple cell blocks within a category block. Some of our guys cheat the system and use VBA to put in names (effectively creating a freeform name) but these won't be inserted when the underlying module is reused or shared, so it's not recommended. Hence, I'd recommend finding a way of inserting cell block-specific range names rather than trying to hack the system. If you have a specific example I'd be more than happy to think-tank it with you.

Cheers, M.

Hey Michael,

On the topic of Filtering, is this still in the too hard basket?

My workaround at the moment is using the =UNIQUE() array functionality to duplicate the category block (I've attached an example if anyone is interested) but it would be great if it became a feature within Modano itself.

Cheers

T

Nifty Tarjei. Beats the old way of copy/paste values into another workbook/worksheet and then filter to find what you want!

I like how the filter automatically picks up the new categories (or alternatively reduces the range it picks up when categories are removed).

Hi Tarjei,

I have questions, this is a brilliant solution.

Can I know what the # in first xlookup parameter do? Notice when dont have the # and de activate the check box all rows are N/A

When replica the solution that in a blank assumption + free form sheet the filter work as if in normal excel, so I try to understand the nuance between this solution vs a plain filter?

This is a pretty cool workaround Tarjei, well played!

We have looked at incorporating filtering into module components but it's turned out to the quite hard for very complex reasons related to how Modano and Excel work together to capture the actions of the user when editing content within module components.

It's also not a top priority for the core financial model types used my the majority of our users, so we've currently logged it as a future development.

We'll get there, but in the interim please be patient. And keep finding awesome workarounds!

;-)

M.

Hi Hengjia,

You use the # sign after a cell to refenrece a range built by either =UNIQUE(), =TRANSPOSE(), =SORT() or FILTER() (there may be others I've not come accross yet). This will then pull the whole list.

These formulas are incredibly powerful to extract data from large data sets, I think of them as a 'live pivots' that will expand or contract depending on the data you are referencing.

I've been playing with these in Modano models to go the other way then my example workbook above (from freefrom sheet to Modano) to for instance extract all entries NOT found in a category block. Say for example you have a large list of accounts you want to forecast. you enter the known accounts in a category block and then set up separate freeform (this is very important, Modano does not like data entered into cells without knowing what it is - which is what say =Unique(list of accounts) will do) which has ALL company accounts (i've been using Power Query to pull this from a central location for this but a normal copy paste table will also work - as long as it is a Table.

You can then combine UNIQUE, FILTER and SORT with a XLOOKUP back to your category block to create a list of any accounts that might have been added to the master list since you last update the model. I then do a count of these "unkowns" (=COUNTA(A2#)), link (not modano link) the counter back to an assumption cell that I make a trigger block cell that will drive the number of rows in a new category block listing all accounts not in the original "proper" category block. The new catgegory block uses an offset to pull the unknows in and the counter will expand and contract as needed. This will give you a thousand warnings about freeform formulas so will not work if you intend to use the modules elsehwere but has proven incredibly useful overt the past couple of months for me.

I apprecaite that is a lot of words and probably very confusing, I'm happy to share a worked example when time allows if anyone is interested.

Next step for me is to find out how I can use the API to trigger all Trigger Cells with one click (I normally use mirrored modules as it is the #1 benefit of Modano in my view). Unless you can add a "Trigger All" button on your toolbar Mike?:-)

T

Thank you Tarjei, I now got it, it is like equivalent of @ for the table formulas. Also tried to reconstruct your method regarding what is newly added to a category as it has lots of user case. I fail to catch up with your thoughts. The number of category items aren't dynamic in my working.

Hi Hengjia,

The only way to make the category block dynamic is to use trigger cells explained here. You can also not add FILTER formulas to Modano sheets as you will most likely end up with a corrupt workbook.

In the attacehd I've created a dummy workbook where "everything" is driven by the data table. I've tried to add some instructions to the data table tab - hopefully they will explain what I'm trying to show. 

The key is to run all Trigger cells to update the model - and correct errors as they appear. Try two things in the file if you are interested:

1. After clearing the two errors, in the data table, add another row with a new account number (one currently not used, say 2000) in either one of the two existing Cost Centres (ABC1 or ABC2) - this should trigger a new error that needs correcting
2. Once that is done, add another row and populate with dummy data for amount, year and account (you can use any account, now or existing), but assign this to cost centre ABC3 (which is not in the model). Then trigger the cell on the control sheet (or open the Trigger Cell Blocks and trigger all cells one at a time) and correct the naming error that appears. Then correct the error you'll get in ABC3 module as there will be a missing account. What it will do is to Mirror ABC2 module (you can read amount how to create "Modules as Categories" in Jun's excellent posts here to get an understanding of how to set this up from scratch)

Hopefully this example will show the concept I'm trying to exlain. Keep in mind that this approach breaks every single rule around best practise Modular workbooks and a file built using this cannot be exported into another modular workbook and is also more error prone as the Modano checks are sidelined to squeeze all this in.

For my use, combining Power Query with these dynamic formulas and the magic of mirroring and linked workbooks in Modano opens near endless possibilites. As an example, I've bulit a Labour model with over 1000 FTEs that change on a monthly basis (power query + dynamic formulas) for 10 different departments (mirroring) while keeping the head count model separate from the payroll data for privacy reasons (Linked workbooks) inn under four weeks. This is replacing a model that took 87 pages of instructions, took over 2 weeks to refresh and came in at over 100mb which needless to say was not built in Modano!

T

Thank you Tarjei! This is such a solid build and also too complex for me at the moment to do a replica. It worked really well to capture the newly added ledger or cost center. Will go on with the recommended reading for trigger cell and category to mirror module posts. Had a workaround to filter module where basic macro is used to assist the task so it kind of work on top of module structure and hide the unqualified rows. This is outcome of recent report which failing to adhere to dashboard -> assumption -> export framework as I basically still could not think purely in Modano's way. Reading your explanation is so helpful.

This is really cool stuff Tarjei!

Love how you're combining Power Query, Modano and now Dynamic Array formulas.

I've only just started playing with DAs, but I'm going to make sure I focus on core for now.

Awesome stuff!

Jun

Just updated to v.15 and my prayers have been answered:

This opens up a whole new world - thank you Modano team!!