Dynamic dependent drop-down list – advanced approach with LOOKUP

WP ThumbThis approach uses the LOOKUP to produce a dynamic dependent drop-down list, which will cater to dependent lists of various input quantities. The application of a dynamic dependent drop-down list, can be applied in a variety of financial models and spreadsheet situations.

Excel AGGREGATEStrategize Financial Modelling – Dynamic dependent Drop-Down List example – Advanced with LOOKUP.xls

 

Problems with a standard dependent drop-down list – using INDIRECT

A standard dependent drop-down list employs the INDIRECT function. This is fine if all the dependent lists contain fixed ranges of variables – that is they won’t change.

But what if our Asia region is expanding? In other words, we are opening up in South Korea, Thailand and Vietnam. For the purposes of this example, we will disregard the obvious need of populating the below Sales Volumes by Business Unit.

Clearly we need a workaround solution, because the addition of these 3 new Business Units renders the existing dependent-drop down list redundant. Our list must be dynamic. The use of the INDIRECT function approach is no longer viable.

Obviously we need a solution, which caters to various dependent lists of altering components.

 

Using a LOOKUP table to achieve a dynamic dependent drop-down list

One solution is the creation of a table of values, based on the precedent value selected in Regions list in cell B7. One column must be copied across, such as range H8 to H17 – across to column J. The title value is sourced from cell B7.

LOOKUP

Next the LOOKUP function must be applied, in order to reference the correct, corresponding value from the 5 region lists. One alternative is to use the HLOOKUP – but this exercise will stick with the LOOKUP function.

It is vital to apply absolute cell referencing to the lookup_vector, whilst applying relative referencing for the result_vector – because we want to reference the corresponding row down the table.

We can see the LOOKUP is returning zeros – if there is not a corresponding business unit. We will format the range to 2 quotation marks – but remember we will need to factor out the zeroes in the next part.

 

Creating a Defined Name for the dependent LOOKUP table

The final element will reference this LOOKUP table, which we just created. The defined name range, listBusUnits, will replace the INDIRECT function – which cannot cater for dynamically changing or varying dependent lists of business unit names.

Go to the Name Manager. Create a New Name. It is called “listBusUnits“.

Name Manager

Take your time and if needs be – feel free pause this video. This referenced formula for the table in column J must be 100% accurate – otherwise we won’t achieve the desired result.

Be aware for the OFFSET part, reference the first cell in the range of Business Unit countries – not the title cell. Remember the COUNTA rather than the COUNT function is used, COUNTA will returns the count of cells that contain any type of cell value (i.e. which are not blank). We must use COUNTIF to disregard those cells containing zeroes – which we formatted to 2 quotation marks before.

 

Changing the source formula in Data Validation

Go to cell D7. Now we must go to Data Validation, and change Source formula to listBusUnits. Let’s check our dependent drop-down list. Great it’s working.

We will add some new, hypothetical business units across the various regions. Perhaps we want to divest or close a business unit – as we will do for the “MidEast“. We now change the source Region, and immediately see the LOOKUP table change too.

We can clearly see the LOOKUP table and dependent drop-down list are changing and flexing correctly.

 

Conclusion

The use of a LOOKUP table and defined name range referencing, can create a dynamic dependent drop-down list for a financial model. This approach will be able to dependent lists, which contain differing ranges of variables – even if these ranges change in scope.

Form control button jumping around

This video focuses on the properties of a Form control button, and in particular the common problem of a macro button moving or re-sizing in a financial model.

The use of a customised form control button, commonly referred to as a macro button, can add another dimension to a financial model’s value to a company. It can be particularly useful if a model developer needs to create a template, which will be rolled out across numerous divisions or business units.

The adoption of customised VBA macros can improve the automation of certain time-consuming, manual tasks in a financial model. Further, a macro can also improve the accuracy and consistency of certain spreadsheet tasks.

Therefore, it is important to understand the various properties settings for an Excel form control button.

Excel AGGREGATEStrategize FM – Form control macro buttons.xlsm

 

Inserting a single button or multiple buttons

In some situations, financial model developers might wish to insert form controls or buttons into a spreadsheet, and then assign a different macro – in order to automate a certain repetitive process. 

The following video outlines three simple buttons or form control options.

 

Why use a Form control versus an ActiveX control?

The answer is simple – Excel for Mac. Although most financial model users are on a DOS computer, which uses MS Excel, there are a growing number of people using Excel for Mac. Excel for Mac does not support ActiveX Controls (sometimes referred to as OCX files), and are likely to generate one or more of the below error messages. 

  • The application “unknown” has unexpectedly quit, because an error of type 3 occurred.
  • Run-time error ‘57121’: Can’t exit design mode because Control ‘CheckBox1’ can not be created.
  • Run-time error ‘32809’: Application-defined or object-defined error
  • Run-time error ‘1004’: Cannot start the source application for this object. There may not be enough memory available

 

Why a macro button or form control jumps around, undocks or even disappears

Curiously Excel’s Form button can resize, undock or move around (and even disappear), if the model developer doesn’t specify the correct properties. Naturally this can impact the user ability of the financial model – especially if the macro button disappears or shrinks to an unmanageable size in the corner of the spreadsheets.

It is crucial to adhere to the correct Form Control properties, in order to prevent  this problem.

 

Form Control Properties

Here are two areas of the Form Control properties to consider:

Object Positioning
The following three options are contingent on the personal needs of the model developer. 

  • Move and size with cells. To ensure the control remains with a cell if the cell moves, and to align its resize whenever the height or width of the cell is altered.
  • Move but don’t size with cells. The form control or macro button will remain with a cell, when the cell moves in the worksheet. Note the button’s size will not be impacted by any change in the cell’s height or width.
  • Don’t move or size with cells. This option will ensure the form control is mutually exclusive; in other words the control’s position and size will not be affected by changes to the cell’s position, height or width.

 

Print object
It is preferable to enable by ticking this check box; even if the form control button doesn’t need to be printed. 

 

Concluding remarks

The preference is to select the first Object Positioning option, “Move and size with cells”, which will guarantee the form control button flexes and moves with changes to the underlying cells.

Format Control - Properties - Preferred

 

To prevent the macro button from re-sizing, undocking or moving around, it is advisable to enable the “Print object” feature – even if the spreadsheet will not require printing.

Shuffling data with HLOOKUP

WP thumb2Here is a great and effective approach to shuffle columns of data. It will use the Excel HLOOKUP function, and not require any VBA macros or complicated re-engineering of this summary worksheet.

As we can see this worksheet contains a summary of financial metrics of various stocks. There a number of steps required, before we can shuffle these columns.

Excel AGGREGATEStrategize FM – Shuffling data with HLOOKUP

 

Steps to Take

Firstly, we must apply the COLUMNS Excel function in row 8.

Second, we will employ the RAND() Excel function in row 9; this will enable the shuffle of dependent data across the columns.

Thirdly, the combination of VLOOKUP and INDIRECT functions will ensure the flexing of outputs. The following video link will demonstrate the use of VLOOKUP and INDIRECT further – Excel VLOOKUP across multiple worksheets.

Finally, the use of HLOOKUP and LARGE functions will guarantee the shuffling of data is dynamic.

 

Important Considerations

Like the third step, with the VLOOKUP and INDIRECT formula, please be careful to type this HLOOKUP formula accurately – otherwise the formula and shuffle may not work. This is the vital piece of this exercise.

It is always prudent to perform a spot check of any complex Excel formula; to ensure the precedent and dependent cells are functioning correctly.

 

One Alternative

There is one alternative to the shuffling of columns of data. It involves changing the LARGE with the SMALL function inside the HLOOKUP formula in the 4th step. As in the following formula in cell C7:

=HLOOKUP(LARGE($C$9:$L$9,C8),$C$9:$L$10,2,FALSE)
becomes
=HLOOKUP(SMALL($C$9:$L$9,C8),$C$9:$L$10,2,FALSE)

 

Conclusion

We will quickly verify the HLOOKUP formula is working – by ensuring each of the 10 ASX stocks are correctly shuffling. Again if we enter on any of the RAND() formula cells in row 9, we can see our data columns are seamlessly shuffling. 

By applying the above 4 steps, we are able to shuffle columns of data; without the use of esoteric VBA macros.

Converting between the two Excel Date Systems in Financial Modelling

WP Front Page SliderExcel supports two date systems, the 1900 and 1904 date system, which can pose potential problems in financial modelling. The 1900 date system is synonymous with Microsoft Excel for Windows, whilst Excel for Mac © (Apple) applies the 1904 date system. 

This financial model is demonstrated in the below video on dealing with the two Excel date systems.

Excel AGGREGATEStrategize FM – Dates from Excel for Mac to MS Excel

 

The Difference

The 1900 date system is represented by 1st January 1900 as its first day, thus a date entered into a Microsoft Excel © financial model will be converted into a serial number, which denotes the number of elapsed days after 1st January 1900. Hence, a capital expenditure for an office building for $5,000,000 accounted for on 20th January 2014, will be designated with a serial number of 41659.

Excel date system

 

On the other hand, the 1904 date system has its first day on 1st January 1904. A date entered into an Excel for Mac © spreadsheet will be translated into a serial number, which is the quantity of days after 1st January 1904. Let’s again illustrate the above $5 million example. If entered into an Excel for Mac © financial model for the date of 20th January 2014; this transaction will be assigned a date serial number of 40197.

$5mill Excel for Mac

 

The Problem

It is unlikely financial modellers will be modelling dates before 1st January 1904, which Excel for Mac © is unable to support because of the early design of Macintosh computers. The problem in a financial model is how the same date, i.e. 20th January 2014, can contain two different serial numbers:

  • 1900 date system: 41659
  • 1904 date system: 40197

A financial model in Microsoft Excel © sourcing financial information from an Excel for Mac ©-sourced spreadsheet, could face problems with this date serial number phenomena.

As the above video demonstrates, capital expenditure transactions recorded in an Excel for Mac © (1904 date system) financial model, and imported into an Microsoft Excel © (1900 date system) will appear as dates that are 1,462 days earlier – the difference between 1st January 1900 and 1st January 1904.

For example the $5 million office building capex will be dated 19th January 2010.

In other words, a difference of 4 years and 1 day.

 

Correcting this Problem

Given these two date systems have a difference of 1,462 days; it is fundamental to be mindful of the impact it could have on a financial model or any business spreadsheet. Some corporations that contain multiple business units or operations across locations or industries, it is possible there will be some financial modelling performed in both Microsoft Excel © and Excel for Mac ©. Hence this is how to correct the serial number problem.

Excel for Mac © into Microsoft Excel ©
These dates are understated by the delta of 1,462 days; therefore 1,462 must be added to the source serial number, which is imported from an Excel for Mac © (1904 date system) into the MS Excel (1900 date system) financial model.