Conditionally put Data Validation lists in some cells

Your example is pretty clear, but what is not clear is how it will change.

Will you be changing which cells are colored, or will you be coloring in some cells that are currently blank/white?

In your example, all colored calls have text, and all cells with text are colored. Will that always be true?

If the answer to the second question is "yes" then this can be done with formulas in data validation. Attached is a solution. The blank cells still have a dropdown, but there are no values in the drop to choose from. This was done by using an unused cell, ZZ99. This adds a named formula GO_list because that formula is not allowed as a Data Validation formula.

If the answer is "no" then you will need VBA. Here is the reason: There are no built-in functions in Excel that tell you whether a cell has a fill color. So if you depend solely on color to identify which cells to allow a dropdown list, you need VBA.

Just for reference, using formatting alone (e.g., fill color) to act as data is not a good practice. It's fine to use it to make something more readable, but color can't be treated as data by Excel, so it's better to use the actual data.

The part that made this possible with formulas is that the heading at the top of the column exactly matches the heading that indicates which column has the entries for the corresponding dropdown list. So the solution doesn't take color into account in any way.

You can highlight the range and apply it once. Just make sure that the cell reference you put in the formula is for cell in the upper-left corner of the range you highlight.

I did it by setting it up in one cell, then copy, then Paste Special > Validation to the rest of the range. But your way works too.



If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking $A addreputationiconsmall.jpg below their profile in any of their posts.

Are you also using Microsoft 365 on your work computer? I can't think of another explanation as to why the same file would work on one computer but not another.

The LET function is way of defining a variable in a formula. In this case I am using it to avoid repeating an entire expression. C is the variable, and represents the expression

Formula:

Please Login or Register  to view this content.

This MATCH function looks at the header for the column of the formatted cell, and looks up the column with the same header that contains the items to include in the dropdown list for that cell. I used C as a mnemonic for "column". It's used twice, so I can just use the letter C instead of repeating the entire expression.

Note that "Sheet1 (2)" is the sheet name of the sheet I added to demonstrate the solution. Your sheet has a different name, so you should update the formula to use the sheet name containing your data.

If you do that and still have a problem, let me know what you are seeing in as much detail as possible. Obviously it would be ideal for me to work with your actual complete file, but I understand there may be reasons you can't share it.

The LET function is not available until Excel 2021. For use on earlier versions you have to use the full formula.

Formula:

Please Login or Register  to view this content.

You still have to update
Sheet1 (2)
to be whatever your actual sheet name is.

Especially since Microsoft's rollout of the 365 subscription product with constant updates, it is essential to tell us what version(s) of Excel you need your solution to run on. There are a lot of new features that have introduced in 365 (and 2021) that aren't available in earlier versions.

In the sample file you sent me, every list has 24 items. How is your file different? B2 is in this column

	B
1	RegABTCell
2	A0A0B4J1G0
Sheet1 (2)

and the corresponding list is here:

	O	P
1	RegABTCell	GO-1
2	A0A0B4J1G0	regulation of tissue remodeling
3	A0A0B4J1G0	regulation of system process
4	A0A0B4J1G0	positive regulation of multicellular organismal process
5	A0A0B4J1G0	regulation of tissue remodeling
6	A1Z198	regulation of cytokine production
7	A1Z198	positive regulation of cytokine production
8	A1Z198	regulation of interleukin-1 beta production
9	A1Z198	regulation of interleukin-1 production
10	A1Z198	positive regulation of multicellular organismal process
11	A1Z198	regulation of cytokine production
12	A2APB8	regulation of organelle organization
13	A2APB8	regulation of organelle organization
14	A8E0Y8	positive regulation of immune system process
15	A8E0Y8	regulation of myeloid leukocyte differentiation
16	A8E0Y8	positive regulation of myeloid leukocyte differentiation
17	A8E0Y8	regulation of cell differentiation
18	A8E0Y8	positive regulation of cell differentiation
19	A8E0Y8	regulation of myeloid cell differentiation
20	A8E0Y8	positive regulation of myeloid cell differentiation
21	A8E0Y8	positive regulation of developmental process
22	A8E0Y8	positive regulation of multicellular organismal process
23	A8E0Y8	regulation of leukocyte differentiation
24	A8E0Y8	regulation of hemopoiesis
25	A8E0Y8	positive regulation of hemopoiesis
Sheet1 (2)

P.S. I may take you up on the beer sometime. I live in Tysons. Where are you in the DMV?

The guidelines for uploading sample sheets said that sheets should be limited to 10-20 columns or so, so I abbreviated my file.

In the version I'm working with, the reference tables vary in length (the shortest is 177 and the longest is 8736) The table with the list values is A1:J783

It appears that the drop-downs in the list cells include every or nearly every term in the corresponding-color reference table.

I apologize for not including these details earlier. I didn't anticipate the solution being SO far over my head
(I'm near Bethesda!)

Originally Posted by beagoodbear

It appears that the drop-downs in the list cells include every or nearly every term in the corresponding-color reference table.

Maybe I'm not getting something--isn't that what you wanted?

If you need further assistance, please remove the SOLVED tag.

I'm working on this but my initial solution is not working. I modified the formula assigned to the name GO_list. The name works in the spreadsheet, but it evaluates to an error when I use the same name in a data validation list. I don't know why. The formula takes the initial formula and extends it using the FILTER function to select only those cells that match the ID in column A.

This formula cannot be used directly in the DV formula, but typically the workaround is to make it a named formula and use the name. Not sure why this isn't working, will continue to look into it.

Formula:

Please Login or Register  to view this content.

(I changed my sheet name to Solution)