var sibErrMsg = {"invalidMail":"Please fill out valid email address","requiredField":"Please fill out required fields","invalidDateFormat":"Please fill out valid date format","invalidSMSFormat":"Please fill out valid phone number"};
var ajax_sib_front_object = {"ajax_url":"https:\/\/excel.tv\/wp-admin\/admin-ajax.php","ajax_nonce":"c44f3884cd","flag_url":"https:\/\/excel.tv\/wp-content\/plugins\/mailin\/img\/flags\/"};
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:
Step 1
Step 2
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.
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.
Jordan Goldmeier is an accomplished data professional with a wealth of experience across various industries. He currently serves as a consultant at Anarchy Data, where he assists businesses in maximizing the capabilities of Excel for financial planning and analysis. Jordan is also an instructor at Full Stack Modeller and a former Adjunct Instructor in Analytics at Wake Forest University. His extensive career has seen him hold positions as the Chief Operations Officer at Excel.TV, Data Science Manager at DataKind, Data Scientist at Dealer Tire and EY, Analytics & Data Vis Developer at The Perduco Group, and Operations Research Analyst at Booz Allen Hamilton. Jordan's background in data analytics and his passion for Excel make him a valuable resource for businesses seeking to improve their data-driven decision-making processes.
var wpilFrontend = {"ajaxUrl":"\/wp-admin\/admin-ajax.php","postId":"192","postType":"post","openInternalInNewTab":"0","openExternalInNewTab":"0","disableClicks":"0","openLinksWithJS":"0","trackAllElementClicks":"0","clicksI18n":{"imageNoText":"Image in link: No Text","imageText":"Image Title: ","noText":"No Anchor Text Found"}};
