Dynamic, Dependent, Validated Lists in Microsoft Excel

FMDA's free 'validated lists' template spreadsheet demonstrates how to drive lists in a dynamic fashion. Not only that, but the template demonstrates how lists can be populated dependent upon input information. 

Validated lists are an absolute must when developing a spreadsheet intended for third-party use. This functionality blocks a user from entering incorrect information in an input form, removing the possibility of input-driven errors.

However, developers are often unaware as to just how flexible these lists can be - partly because it takes a bit of tinkering to get it right!

The first lesson to learn is how to create 'dynamic named ranges'. These ranges grow larger or smaller in their row-column dimensions dependent upon input information. These are demonstrated in the free template.

The second lesson to learn is slightly less intuitive; by using the 'sumproduct()' function in a rather odd fashion validated lists become a whole lot more flexible! Again, these techniques are demonstrated in our free template. You can download the free template here.

FMDA Consultancy provide training and consultancy services for Financial Modelling and Data Analysis. Contact us at fmdaconsultancy@gmail.com.