December 24

Directly link Excel form controls to backend data with dynamic references

Form controls are great for reporting information about groups of items, like a list of programs or accounts. They are often used on Excel dashboards and reports that demand interactive capabilities. One such type of capability provides the user with a list of items to choose from. When the user makes a selection, a macro is executed that populates a table holding referenced values. Those values are linked to a series of form controls on the frontend. This interactivity is displayed below:

step1
Step 1
step2
Step 2

step3
Step 3

There is a final step, which I haven’t included. The user would make changes to the project under the Options table. They would press a “Save” button and their changes would be copied from the Linked Values table back onto the backend data in the column corresponding to the selected project using VBA.

The No-VBA way

There’s nothing wrong with this method in and of itself, but I want to propose a method that requires no VBA. The advantage of this new method is that it links directly to the data itself and bypasses the need for the Linked Values table. We can do this by allowing the form controls to take advantage of dynamic references.

Typically, form controls can only do direct, absolute references. You cannot, for example, use VLOOKUP or INDEX within the source field of a form control. However, you can use a named ranges.

Let’s do it!

First, we give that ‘index’ field above a named. How about selection? Next, we create four named ranges to correspond to the form control checkboxes. Stage_1 to Stage_4 are those new named ranges.

namedreferences

As you can see from the picture, I use the fourth row to connect to checkbox Stage 4 and the selection value to inform Excel to pull from the fourth column in the backend data (which is Project 4, if you recall).

Finally, I can simply link these named ranges to their associated checkboxes:

Using this method, changes to the checkbox automatically change the backend data. There is no intermediate table required — like the Linked Values table above — to interface between the frontend and the backend.

That’s all for now – have a happy and health holiday season!

Update 25 December:
Make sure to see the download file – Direct Links.xlsm.


Tags


You may also like

February 17, 2022

Edit Excel Formulas in WORD – Spreadsheet Tips and Tricks

February 8, 2021

51: Oz du Soleil & the Global Excel Summit 2021

January 29, 2021

Global Excel Summit 2021

January 22, 2021

50: Randy Austin – Excel for Freelancers

January 8, 2021

49: Theresa Estrada – Microsoft Principal Program Manager Lead

May 26, 2020

All Excel LOOKUPs Explained
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.

>