Data Validation - character limit
Hi,
I'm attempting to build a worksheet that uses dependent lists. I have set column C to poulate from a list on a supporting worksheet. Now I am trying to programme column D to display a list which is defined by the selection made in column C. My usual method is to programme it via Data/Validation and then enter the formula in the source box. However, the formula I'm trying to enter is 446 characters long:
IF(C5="Administration",TL1,IF(C5="Business Continuity Management",TL2,IF(C5="Corporate Insurances",TL3,IF(C5="Group Reporting",TL4,IF(C5="ICA",TL5,IF(C5="Incident Management",TL6,IF(C5="Internal Audit",TL7,IF(C5="ISMS",TL8,IF(C5="Management",TL9,IF(C5="Procedures Management",TL10,IF(C5="Risk Control Tools",TL11,IF(C5="Risk Register",TL12,IF)C5="Solvency II",TL13,IF(C5="TCF",TL14,IF(C5="Training",TL15,IF(C5="Travel",TL16,Family))))))))))))))))
. . . and the Data / Validation / Source box only allows 255 characters.
Is there a way to extend the character limit of the Data / Validation / Source box?
- or -
Is there another way to achieve the same result?
Thanks for your help.
Are the lists themselves dynamic ?
If not then you can get away with INDIRECT I suspect...
=INDIRECT("TL"&MATCH(C5,nameusedforlistinC,0))
the bit in red would be the named range you've used for your validation list in column C - assuming you used a named range of course... if not create one that contains Administration, Business Continuity Management etc etc...
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks