Excel functions

Got Formula?

Have you looked at the formulas used with the dropdowns created using the Insert from SL feature? There is some strange looking code there. When XLstatements was designed, we set ourselves a requirement that no VBA code (“macros”) would be necessary in reporting workbooks—all the VBA code is instead in the Excel add-in (XLstatements.xlam). This presented a problem when we create dropdown lists for subaccounts: we wanted this list to only show subaccounts (or subaccount segment combinations) that are actually used with Dynamics SL, but still allow you to see and, optionally, include other subaccounts. In other words, we needed to use a filtered list for the dropdown choices. As you probably know, the lists are created from tables on the Structures worksheet. (A Structures worksheet is automatically created when a dropdown list is first created using the Insert from SL feature.) If you look at a subaccount table on the Structures worksheet, it looks something like this:

subacct

I am using the Solomon demo data here; the demo database included with Dynamics SL but with data from the last millennium (back when the product was called Solomon), and that has many subaccounts defined but that have never been used. This causes several notable effects with the above Structures table. Can you see that many rows are missing from the table? Rows 33, 35, 40-42 and 46-67 are not there, but if you look closely you can see double lines on the row number heading where lines have been skipped. Note that there is a filter on the Description column (that’s the funnel icon visible on its header). If I click on that filter icon, I see a dropdown list of values:

filter

As you can see, not all values are selected. The Select All button is filled in, rather than checked, which means that only some rows are selected. Administration-UK is not selected in the above screenshot. That’s because there were no transactions using that subaccount. When the table was created, a filter was also applied selecting only values that have been used. This is to keep the dropdown list that uses this table from including irrelevant information. So the table actually includes the values but the filter hides them: the “missing” row 13 is for Administration-UK.

But an Excel dropdown list range (created using Excel’s Data Validation functionality) will include all values in the table, even if they have been hidden by using a filter. So how did we manage this? It would have been easy (for us) if we could include VBA code on this workbook, but that wouldn’t fit with the requirement we set ourselves.

If we look at the dropdown created from this table, this is what we see:

subddtop

subddbottom

I did two screenshots so the entire dropdown list is visible (with three values overlapping). There are thirteen values listed (and yes, the demo data has two subaccounts with the same name) and these are the same thirteen descriptions in our filtered table. The Data Validation in the dropdown cell (B3, in this example) is:

validation

The Source for the list is:

=NR_0060_SubAccts

That’s a named range, but it’s a special one that you cannot just Go To. If we look at it using Name Manager, this is how it’s defined:

dynamicrange

So there’s a complex-looking formula instead of just a range like $A$2:$B$4. This is what is known as a dynamic named range, and it is an advanced Excel technique. We can see that the formula is referring to the List column of our table several times: that’s where it’s getting the values used in the dropdown list. So what’s with that List column? It has only eleven rows populated. If we look at the first entry in that column, we see another complex formula:

{=IFERROR(INDEX([Description],SMALL((IF([Print],ROW(INDIRECT("1:"&ROWS([Description]))))),ROW(A1)),1),"")}

The curly braces around the formula mean it’s an array formula (another advanced Excel technique). Again, Excel formulas cannot tell what rows have been filtered from a table, but the XLstatements Insert from Excel feature sets the Print column value to TRUE for rows that should be included in the dropdown list and FALSE for rows that are to be excluded. Using this array formula means that only the TRUE rows will be included in the List column, and FALSE rows will be left out. Unlike the table filter functionality, this formula removes, rather than hiding, such rows. There are only eleven values visible in the list column for the table, because rows 33 and 35 are hidden due to the filter. So there are actually thirteen rows in that column, and that’s what is being used for the dynamic range. The above array formula removes any values that have FALSE for print and removes any such “gaps” from the list. The dynamic range adjusts the dropdown list range to the length of this list. Whew! Nobody said this was easy…

If you start using a subaccount that had never been used when the Structures table was created, you can either recreate the table by using Insert from SL again or you can simply use the table’s filter and select the subaccount. If you do that, you may notice something mysterious: once you select a worksheet other than the Structures sheet, that row’s Print value will change from FALSE to TRUE. This is due to event code in the XLstatements add-in.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s