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.
