Can you Automatically Update Formulas and Data Validation Between 2 cells in Excel Using V

In Excel, I have the following formula in cell D14:

Please Login or Register  to view this content.

This formula returns the name based on the contact entered in D13. I now want to extend this functionality, so that I can enter a name in D14, and the corresponding contact will appear in D13 using the lookup. Is it possible to achieve?



I want to automate this process using VBA. Specifically, if I make a change in D13, the formula in D14 should turn into searching. Similarly, if I make a change in D14, the formula in D13 should adjust accordingly.

Additionally, I have data validation applied to D13 with the formula '=Cust_List'. So, I also want the data validation in D14 to change to '=Cust_List_Name' when it will be used for search.

Here's the complete VBA solution:

Setup a Worksheet Change Event
This event will monitor changes in D13 and D14 and switch their formulas and data validation dynamically.

Adjust Formulas Dynamically
When D13 is changed, D14 will show the corresponding name. When D14 is changed, D13 will show the corresponding contact.

Modify Data Validation Dynamically
Update the data validation of D14 to reference Cust_List_Name when it is being used for searching.

Please Login or Register  to view this content.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.
For new members: you will not be able to post a full URL, so remove the https://www. section and add one or two breaks to the rest of the address. A Moderator or Admin will then fix the link for you.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. As you are new, I shall do it for you this time: https://www.mrexcel.com/board/thread...g-vba.1267827/)