Linked Data Validation Cells

[dtpham]

Working on a project where the user must enter the Refrigerant Type. There are three ways to identify a Refrigerant, the Number, Chemical Name, and Chemical Formula. I want to create three separate Data Validation Cells for each Identification method. If they decide to choose the Refrigerant by the Number, then Chemical Name and Chemical Formula should be automatically selected depending on the corresponding number. If they decide to choose Refrigerant by the Chemical Name, then Number and Chemical Formula should be automatically selected, etc.

Capture.PNG

The attached image is a small sample of Refrigerants that I would like to include.

Any help would be appreciated!!

[6StringJazzer]

To put data validation in a cell means that the user is going to type in data. To have it automatically set a value based on another cell means it has to have a formula. You can't have both in the same cell.

That means that you need a macro. I can help further if you provide your actual file. Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[dtpham]

The "Refrigerants" worksheet lists the majority(EDIT: Now complete list) of refrigerants that I'm working with. The next sheet labeled "Sample" is the same list given in the screenshot.
Haven't edited the worksheet to look presentable, if the data seems unorganized, I pulled the information from this website that may be clearer to read:

https://www.ashrae.org/standards-res...t-designations

Your help is very much appreciated!

[6StringJazzer]

You have two different lists on the Refrigerants sheet. I am just using the first one, because that matches your sample.

First I created three named ranges: NumberList, ChemicalNameList, ChemicalFormulaList. I used these to create the three cells with Data Validation that the user can select from.

I added a VBA Sub called Worksheet_Change that will detect when the user selects one of the three values. When that happens, it looks up the other two values and fills them in.

If you change anything about how the Refrigerants sheet is organized, or anything about how the Select sheet is organized, the code needs to be updated to reflect the changes.

[dtpham]

Extremely helpful!! Exactly what I was looking for, thank you very much!

Is there a way I can view the said VBA Sub, Worksheet_Change? I would like to study the code so that I can apply it to the second list or adjust the lists if needed. Or if there were any references that you used that I could utilize as well.

[6StringJazzer]

(Use quotes when needed to clarify what you are replying to but please don't quote an entire post with each reply. Quotes tend to clutter a thread.)

I don't know how much to explain because I don't know how much you know to start with. I can't give you a single reference for VBA but I first learned it from an excellent book by John Walkenbach.

Take a look here, and go to the code for the Refrigerants sheet.