UDF that returns a data validation list

[SlipEternal]

I have a series of drop-down lists. Most of them are populated with named ranges. However, sometimes, the criteria changes, and they need to be populated with a custom list. I have it set up currently where I have off-screen formulas. If a cell includes a semicolon, then I assume I need to build a custom range. So, the next 50 cells are devoted to turning the semicolon-delimited list into a range that can be used for the data validation. So, my data validation formulas are rather complex:

Please Login or Register  to view this content.

For this example, WW10 contains either the name of the range or a semicolon-delimited list of values that should be present in the range. So, XD10 is a formula to count the number of semicolons in WW10. If it is 0, then that means that it is just a range name, and INDIRECT(WW10) returns that range. But, if it is a semicolon-delimited list, then I build that list dynamically starting from XK10 to however far out I need to go to the right. I can then copy this down and the validation formula updates automatically for the hundred and fifty rows that I need this validation for. Each row has ten of these potentially dynamic lists. It is rare they will be populated by a dynamic list, so for the most part, the formulas are not needed. So, multiplying out, that is 150x10x51=76,500 cells with formulas. And at first, this was no big deal. But, over time I have had to add additional copies of this same sheet, each with similar formulas. The workbook is starting to slow down. Is there a way to replace all of this manual manipulation with a UDF that could use

Please Login or Register  to view this content.

If that returned a range, that would potentially solve all of my problems. I would not need 76,500 formulas per page, which I think has the potential to speed things up a bit. At this point, I would be able to remove close to a million formulas if that were the case (out of the million, maybe a thousand are actually being used for this purpose). So, is there a way to return a Range object that does not actually point to a range, but makes Excel think that it does?

Attached is a simple example of the current functionality.

Edit: Since a UDF referenced from a cell cannot update another range, this may not be possible.

[SlipEternal]

As I research this further, it has become apparent to me that this is not possible through VBA. I appreciate anyone who looked into it for me, but I am going to mark this as Solved, as the solution is to keep the existing structure, even if it is a bit slow.

[XLent]

Hi, as you say, you cannot achieve this with a UDF but you could, perhaps, get away with using the Selection Change event?

Referencing your sample file, if you right click on Sheet1 -> View Code - paste below into resulting window.

You should then find that as you select the DV cells the lists will reflect contents of B, and if a semi-colon delimited list you will get those contents
(e.g. if you change B3 to include additional items, and select A3 so the DV list should update accordingly)

Of course, there is some overhead with the Selection event - so whether or not it's worth pursuing will be up to you...

Please Login or Register  to view this content.

[SlipEternal]

Hi, as you say, you cannot achieve this with a UDF but you could, perhaps, get away with using the Selection Change event?

Referencing your sample file, if you right click on Sheet1 -> View Code - paste below into resulting window.

You should then find that as you select the DV cells the lists will reflect contents of B, and if a semi-colon delimited list you will get those contents
(e.g. if you change B3 to include additional items, and select A3 so the DV list should update accordingly)

Of course, there is some overhead with the Selection event - so whether or not it's worth pursuing will be up to you...

This is an excellent suggestion. I have been trying to avoid event handlers because the users sometimes go too quickly, and the VBA does not update in time, allowing the user to choose options that should be impossible. Given the variety of potential inputs, this could cause calculation issues later that could go uncaught. Still, an excellent suggestion, and this would usually be a great solution.

[SlipEternal]

I actually went back and redesigned the bulk of this. The benefit of custom lists that turn into data validation is that users can create their own data validation for each other. But, these custom lists are not typically needed. The existing data validation lists are typically good enough. But, for the occasion when the custom list was needed, I just created dynamic named ranges where users could enter their own list. Each such list is labeled letting the user know which named list to use for the data validation cell. So, instead of users creating custom semicolon-delimited lists, now they just add values to preexisting lists that are not tied to any data validation, then assign that list. Even if I need a thousand custom lists, it is unbelievably faster than the extra million or so formulas that I was using. The entire spreadsheet is faster and more stable. I don't know why I did not think of this sooner. It is a more obvious approach, anyway, and does not require any VBA at all.