+ Reply to Thread
Results 1 to 3 of 3

IF OR value to be either a text string or dropdown list in same cell.

  1. #1
    Registered User
    Join Date
    08-20-2018
    Location
    Wirral, England
    MS-Off Ver
    2019
    Posts
    15

    Angry IF OR value to be either a text string or dropdown list in same cell.

    I am really struggling with this and have looked on numerous sites including this one and what i am trying to achieve I do not think I am alone.

    Simply put I want to check two separate cell on the same line to see if they match
    1. check if a cell has one value or other and if so shown text
    2. next if it does not match check a different cell for a match, if so show text if not
    3. show a drop down list from a named range

    I have tried the following in both the cell directly or via data validation list and none give the desired result. Either I get an error with no suggestions, a blank cell or the drop down list even when the first criteria are met
    Please Login or Register  to view this content.
    I am sure there will be question so please ask! thanks in advance and stay safe!

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: IF OR value to be either a text string or dropdown list in same cell.

    The data validation need is separate from the formulaic need. These two ideas can coexist, but under normal circumstanced, if a user ever uses the DROP DOWN in the cell to make a selection, the formula that was in the cell previously is gone, forever.

    Of course, VBA can be used to watchdog everything going on in the sheet and put the formula back into the cell in certain circumstances, but employing VBA is a whole new level of plumbing that will need to be maintained in perpetuity. Not everyone can tolerate that extra level of maintenance, or simply can't allow macros to be running on their sheets at all. It's not for everyone.

    So, just put together the formula you need and put that in the cell first.

    I'm thinking:

    =IF(OR(G14="POC", G14="COTS"),"N/A",IF(G14="Assembly","PDF","Select:"))

    That formula will cause the word "Select:" to appear when the drop down is needed.

    Then add your Data Validation. If you want it to be a one-time choice, and assuming this were placed on cell H14, use this formula as your DV source:

    =IF(H14="Select:", ModelType)

    Another route which allow you to use the drop down over and over again:
    =IF(AND(H14<>"N/A", H14<>"PDF"), ModelType)

  3. #3
    Registered User
    Join Date
    08-20-2018
    Location
    Wirral, England
    MS-Off Ver
    2019
    Posts
    15

    Re: IF OR value to be either a text string or dropdown list in same cell.

    Big thank you for the response, when it is explained in this manner it make total sense.

    The only draw back is when an item has been selected in the drop down cell G can change but it will not update cell H. In my case it is not required but would be good to figure out for future possibilities

+ 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. Replies: 6
    Last Post: 02-02-2016, 04:26 PM
  2. Remove Dropdown List if Text in adjacent cell
    By brent_milne in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-17-2014, 05:28 PM
  3. [SOLVED] CountIf cell copntains specific text generated dropdown list
    By michael.reynolds1775 in forum Excel General
    Replies: 2
    Last Post: 11-19-2014, 05:27 PM
  4. Replies: 2
    Last Post: 03-31-2014, 01:32 AM
  5. [SOLVED] Macro to jump to a cell that matches the text selected from a dropdown list.
    By agalvan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-24-2014, 06:28 PM
  6. Email user from dropdown list, text from cell in corresponding row??
    By ccomito1223 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-15-2010, 08:42 AM
  7. Replies: 21
    Last Post: 12-02-2009, 03:27 PM

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