Standardizing dropdown options across multiple worksheets

Alexander Orsini

I've seen some posts about this and most people ask for a use case. I have a sheet called LOB, which lists the lines of business and the associated leader for each LOB. In many sheets (projects, budgets, portfolio intake, etc), we need to identify the associated LOB for items in those worksheets and then vlookup the associated leader so we can send alerts, request updates, and apply other workflows. Manually updating the drop down LOB options in every sheet is painful to do, especially when adding or removing a LOB. I would rather read the current options from the LOB worksheet. We've tried the idea of reversing this to select the leader first and then vlookup the LOB, but most people know the LOB they need to involve and don't always know who the leader is. And leaders change more than LOB so we prefer to have the leader change dynamically via the vlookup, making the LOB the stable content we want to select when assigning.

I can also make a case for using another sheet to drive dropdowns: harmonizing issue and risk categories. We would like to have a master risk category worksheet and a master issue category worksheet and all project risk and issue lists use those to select dropdown values so multi-sheet reports can display and group properly based on everyone using the same options across teams.

This is good governance and simplifies the work to standardize content. Any suggestions to do this are greatly appreciated.

Andrée Starå

Hi @Alexander Orsini

I hope you're well and safe!

I developed a solution recently for a client using the Premium app, Data Shuttle.

Is that an option?

I hope that helps!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Alexander Orsini

Hi @Andrée Starå, I prefer this be something as simple as what I can do without add-ons or experts in Excel. Seems a fairly simple and intuitive thing people would likely need to do as a common formula. If we can do vlookups, the simpler concept of this is to list the information in the target vlookup sheet for selection. Otherwise, I have to manually check if the content in the vlookup sheet is matched correctly in the dropdown options in each sheet. Any data entry error in either the sheet used for vlookup or in the manually created dropdown list of options in each sheet renders the vlookup inaccurate. And then people revert back to Excel because something simple like this requires an expert or an add-on. Using Data Shuttle therefore isn't an option.