A client called a few days ago because she could no longer open a Word document by double-clicking it in Windows Explorer. How would you troubleshoot this problem and what’s the most likely culprit?
In the last challenge, we asked…
How would you create a dynamic Data Validation range? Time to ‘fess up folks — I’ve stumped myself! I was counting on you guys to solve this one for me, and you… you called me out on my deception.
A couple of you suggested the Offset() formula for creating a dynamic range. Initially, I too had the Offset() solution in mind, but it doesn’t work. Okay, I’ll say honestly — I can’t make it work. This happens to me occasionally; the idea works in my head, but not in practice! Here’s where I’m stumped: The Data Validation feature works with a selected range and that range is static. I can find no way to update it without using code that must be executed with each new entry. That’s not very dynamic. If you can turn that original static range into a dynamic range that requires no macro for updating, please post step-by-step instructions so we can all grovel at your feet. I mean… acknowledge your superior Excel skills!
My suggestion, for now, would be to route the data input process through a user form where code maintains the list or checks the validity of each new entry and to not use the Data Validation feature at all! It certainly wasn’t the first time (and surely won’t be the last time) that my initial strategy failed me.