Excel Dynamic Data Validation Based on Adjacent Column

0
votes

I am trying to piece together some VBA code that would apply data validation (creating a list to use) to a cell based on the value of the cell next to it. I have this functionality working in a Google Spreadsheet which I just linked but I don't know anything about VB so I'm struggling with this.

The layout is a little odd with the spreadsheet since the sheet(s) that need this validation have the headers of the rows starting at row 2.

The basics of what's going on is:

  • The header of the column that is determining the validation is called "Attribute Group". I want to avoid needing a fixed position so I went with the header name instead of a column index
  • When a value changes in "Attribute Group" the cell adjacent to it to the right will have a dropdown created with values corresponding to the value in "Attribute Group"

Please check out the linked spreadsheet to see this functionality in action. I'm trying to get as close to this as possible in Excel.

Edit: The main question is: how do you create dynamic data validation in Microsoft Excel?

2
excelvbavalidation
This has nothing to do with google-apps-script so I removed the tag. – Cooper
Use a named range (Eg "MyDataRng") for the range of cells in the Attribute Group then in a list type data validation enter =MyDataRng – Tin Bum
@Cooper Thanks for the edit. I wasn't sure if the tag should be used or not but thought it safer to add. – User_Undefined
@user1302114 I'm not quite sure what you mean with the "... in a list type data validation...". – User_Undefined
In excel you can add data validation to any cell and you can tell it to use a range of cells as the list of valid entries ... it's on the Data tab/menu ,,, You name the range cos I find that easier to enter in the validation form/window/box/field ... Google Excel Data Validation List – Tin Bum

2 Answers

0
votes

Unfortunately I don't have time to get this working exactly as I would like.

The solution I have now was to create tables for each column that had data that would correspond with the appropriate "Attribute Group".

Insert > Table

Rename table to match corresponding "Attribute Group" value, removing spaces and special characters

Remove duplicates to get rid of most blank cells then Resize Table if needed to get rid of any others

Then I added data validation in cell E3 using =indirect() as well as substitute() since my "Attribute Group" values had spaces and special characters.

Data > Data Validation

Under "Settings" I used "List" in the Allow section

The source was =INDIRECT(SUBSTITUTE(SUBSTITUTE($D3," ",""),"&","And"))

Then I copied cell E3 and pasted all the way down. This method solves my current issue but doesn't allow for the level of flexibility that I was hoping to accomplish.

-1
votes

(Edit Addition - following down vote) Answered in comments but also adding an answer to eliminate this from list of un-answered questions ..... not looking for brownie points (End of Edit)

Just so this is removed from un-answered questions ..... Use a named range (Eg "MyDataRng") for the range of cells in the Attribute Group then in a list type data validation enter =MyDataRng