+ Reply to Thread
Results 1 to 20 of 20

Conditionally put Data Validation lists in some cells

  1. #1
    Registered User
    Join Date
    07-22-2024
    Location
    USA
    MS-Off Ver
    Excel in Office 365
    Posts
    10

    Conditionally put Data Validation lists in some cells

    Background:

    For ease, I will call the leftmost table in my spreadsheet the Main Table. I have a list of ~780 IDs, listed as row names in the Main Table. (Each row label is an ID)

    Each of the ~780 IDs has potential to map to one or more terms (indicated in different, solid-colored tables which become visible if you slide over).
    If an ID maps to multiple terms in a list, that ID in the left column will be listed in duplicate, with each individual term next to it in the right column.

    Not every term will map to every color table, which is what I want to represent by coloring/not coloring cells in each ID row in the Main Table

    What I want to do:

    I want any cell that IS filled/colored-in to have a drop-down list containing terms from the corresponding-colored tables' right column. I would like blank/white cells to remain blank, as there is nothing to put in a list

    The first few rows of the Main Table demonstrate what I want for the whole Main Table

    I am open to formula-based or macro-based solutions, but will need a little extra guidance if given a macro. (I'm still quite new to macros)

    Thanks in advance for any help you guys have to offer!
    id-color-mapping-example.xlsx

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,130

    Re: 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.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    07-22-2024
    Location
    USA
    MS-Off Ver
    Excel in Office 365
    Posts
    10

    Re: Conditionally put Data Validation lists in some cells

    Will you be changing which cells are colored, or will you be coloring in some cells that are currently blank/white?
    No, I pre-colored everything I needed to in the Main Table. Cells with text and color will remain that way, and blank/white cells will remain blank/white. I worded that strangely, I apologize.

    In your example, all colored calls have text, and all cells with text are colored. Will that always be true?
    Yes! I initially planned to have the cells blank, but it became clear quickly that this wouldn't fly.

    Thank you so much for your solution! I really appreciate your help, this has been driving me crazy for a week

    Regarding "using formatting to act as data", I know you're right, this is a bit of a crutch for me. Sometimes hearing it from someone else helps it sink in though, thank you

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,130

    Re: Conditionally put Data Validation lists in some cells

    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.

  5. #5
    Registered User
    Join Date
    07-22-2024
    Location
    USA
    MS-Off Ver
    Excel in Office 365
    Posts
    10

    Re: Conditionally put Data Validation lists in some cells

    Quick follow-up question: do I need to manually enter the formula (=IF(TRIM(B2)="",zz99,GO_list) manually for every cell? Or can I highlight the range I want and then apply it to all?

    Thank you for your patience!

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,130

    Re: Conditionally put Data Validation lists in some cells

    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.
    Last edited by 6StringJazzer; 07-23-2024 at 10:22 AM.

  7. #7
    Registered User
    Join Date
    07-22-2024
    Location
    USA
    MS-Off Ver
    Excel in Office 365
    Posts
    10

    Re: Conditionally put Data Validation lists in some cells

    Thank you so much! I'll resolve this thread

  8. #8
    Registered User
    Join Date
    07-22-2024
    Location
    USA
    MS-Off Ver
    Excel in Office 365
    Posts
    10

    Re: Conditionally put Data Validation lists in some cells

    Another quick question:

    What does the "C" in this formula signify?

    =LET(C,MATCH('Sheet1 (2)'!B$1,'Sheet1 (2)'!$O$1:$AP$1,0),INDEX('Sheet1 (2)'!$P$2:$AQ$2,1,C):INDEX('Sheet1 (2)'!$P$25:$AQ$25,1,C))

    I am trying to replicate this in my larger spreadsheet but I keep getting told there are errors in the formula (the C's)

    Everything works well in the file you attached, but I emailed that file to my work computer and now the drop-down lists are all empty.

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,130

    Re: Conditionally put Data Validation lists in some cells

    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: copy to clipboard
    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.

  10. #10
    Registered User
    Join Date
    07-22-2024
    Location
    USA
    MS-Off Ver
    Excel in Office 365
    Posts
    10

    Re: Conditionally put Data Validation lists in some cells

    Okay got it, C = MATCH('Sheet1 (2)'!B$1,'Sheet1 (2)'!$O$1:$AP$1,0)

    I am just as confused! But I checked and I am (apparently) using Professional Plus 2019 at work. (If this is the issue, I'll just work it out with my boss and use my PC)

    On the work computer, your formula as it appears in the "beagoodbear=id-color-mapping-example.xlsx‎ " file returns an error message ("there is a problem with this formula...") even if I don't modify it. Excel won't let me close the Name Manager without making some sort of change if I paste that formula in. I also get an error message when I enter (=IF(TRIM(B2)="",zz99,GO_list) in the Source box. ("list source must be a delimited list, or a reference to single row or column")

  11. #11
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,130

    Re: Conditionally put Data Validation lists in some cells

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

    Formula: copy to clipboard
    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.

  12. #12
    Registered User
    Join Date
    07-22-2024
    Location
    USA
    MS-Off Ver
    Excel in Office 365
    Posts
    10

    Re: Conditionally put Data Validation lists in some cells

    Thank you for being so patient. I made an assumption about the version of Excel but I've certainly learned my lesson.

    The new formula worked! One last thing: I think the lists for each filled-in cell have more terms than they should (B2 should only have 4 terms but it has many more than that) Any idea why this may be happening?

    Thanks again for your help and patience!! I owe you a beer for sure

  13. #13
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,130

    Re: Conditionally put Data Validation lists in some cells

    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)

  14. #14
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,130

    Re: Conditionally put Data Validation lists in some cells

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

  15. #15
    Registered User
    Join Date
    07-22-2024
    Location
    USA
    MS-Off Ver
    Excel in Office 365
    Posts
    10

    Re: Conditionally put Data Validation lists in some cells

    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!)
    Last edited by beagoodbear; 07-23-2024 at 06:41 PM.

  16. #16
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,130

    Re: Conditionally put Data Validation lists in some cells

    Quote Originally Posted by beagoodbear View Post
    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?

  17. #17
    Registered User
    Join Date
    07-22-2024
    Location
    USA
    MS-Off Ver
    Excel in Office 365
    Posts
    10

    Re: Conditionally put Data Validation lists in some cells

    Not quite

    In my example file, in the table with the drop down lists, each row (let's use A0A0B4J1G0) has only the terms that match that row number in each reference table. So for the lavender cell (B2) containing A0A0B4J1G0, that would be four terms. (There are only 4 instances of A0A0B4J1G0 in the lavender reference table.
    If you click on D2 in that same table, you should see four totally different terms because D2 uses a different (pink) reference table.

    My goal is to extract only the terms that match the row ID from each reference table.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,665

    Re: Conditionally put Data Validation lists in some cells

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  19. #19
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,130

    Re: Conditionally put Data Validation lists in some cells

    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: copy to clipboard
    Please Login or Register  to view this content.


    (I changed my sheet name to Solution)

  20. #20
    Registered User
    Join Date
    07-22-2024
    Location
    USA
    MS-Off Ver
    Excel in Office 365
    Posts
    10

    Re: Conditionally put Data Validation lists in some cells

    I really appreciate your help with this! Thank you

    I gave that formula a shot and it returned an error for me too (had to try!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Command Button w/ VBA code to reset Data Validation dropdown lists & manual entry cells
    By Dellpanther in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-28-2019, 04:41 PM
  2. Delete row - but to include any cells that have data validation / lists applied.
    By mattydboom1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-06-2018, 09:18 AM
  3. Default Value in Multiple Data Validation lists based on another cells value
    By Angelammarten in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-04-2016, 10:38 PM
  4. Replies: 2
    Last Post: 09-07-2013, 03:55 AM
  5. how to ignore blank cells in creatig dropdown lists (validation lists)
    By Elainefish in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-02-2013, 07:45 AM
  6. Replies: 6
    Last Post: 01-09-2012, 02:29 AM
  7. Replies: 2
    Last Post: 07-09-2010, 12:34 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1