+ Reply to Thread
Results 1 to 8 of 8

VBA: Trying to display a specific string if INDIRECT function does not find the Ref

  1. #1
    Registered User
    Join Date
    04-19-2019
    Location
    america
    MS-Off Ver
    2016
    Posts
    4

    VBA: Trying to display a specific string if INDIRECT function does not find the Ref

    I am using a series of dependent lists, I simplified my problem in the included sample sheet.

    First column you select a fruit from a list of fruits. (Banana, Apple)
    Based on the fruit you select in Column "A", the list in Column "B" changes to show menu items specific to the fruit from Column "A".
    I use named ranges to create my lists as you can see in the Name Manager.
    I use the INDIRECT function inside the Data Validation menu to reference the list relevant to your selection. =INDIRECT($A$10 & "_List")

    Here's my issue:
    Some fruit, like "Apple" in Column "A", has no associated list to populate Column "B".
    When the user selects Apple, I would like column "B" to display a specific string such as a "Unavailable" over a grey background.

    I'd rather not create an "Apple_List" with one item labeled "Unavailable" as I have a hundreds of fruit and I'd rather not create redundant "unavailable" lists and named them all properly just to get this effect...
    The other issue with that would be that the string "Unavailable" wouldn't populate the cell automatically when the unavailable fruit is selected in Column "A"... I'd have to click the cell and drop it down to see it appear, even if it was the only item in that list.

    I tried a few other things like using an IFERROR formula or an ISREF but couldn't get those to work, I looked into the ERROR tab of the data validation but that only seems to let you write an error message... I'm thinking this should be simple enough and I'm just missing something glaring, like asking where the cookies are in a store while turning my back on the cookie shelf...?

    If I can get "Unavailable" to appear in the cell instead of a list, I can then create a conditional formatting rule to get the grey background.

    Thank you for taking the time to read and offering your help if you have an idea on how to achieve this !
    Attached Files Attached Files

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Trying to display a specific string if INDIRECT function does not find the Ref

    Here's a way but not perfect. Create a new list for when there is No List at all and have unavailable as the only drop down. Create a new list for the list to be used so your indirect formula isn't in the data validation but is in a worksheet formula so you can manage the error handling.

    See attached as it might be easier.
    Happy with my advice? Click on the * reputation button below

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Trying to display a specific string if INDIRECT function does not find the Ref

    The other issue with that would be that the string "Unavailable" wouldn't populate the cell automatically when the unavailable fruit is selected in Column "A"... I'd have to click the cell and drop it down to see it appear, even if it was the only item in that list.
    A cell cannot contain both a formula and Data Validation, at least it cannot persist in that state, the latter is for a user to effectively select a Constant which would overwrite the formula.

    To do exactly what you want you would need to use VBA.

    FWIW, using Crooza's file and leveraging the "No_List" named range, you could use the below as your DV source (i.e. you do not require the E3:E6 range)

    =INDIRECT(CHOOSE(1+ISREF(INDIRECT($A10&"_list")),"No_List",$A10&"_List"))

    the above would, effectively, generate a DV list of just "Unavailable" for any item devoid of it's own individual list however, as noted, you would have to select the value via drop down.
    {similarly, with default DV behaviour, if you change the value driving the dynamic validation the previous selection will not "reset" or be flagged as invalid -- you can use CF rules to flag or VBA to physically alter the selection state}
    Last edited by XLent; 04-24-2019 at 04:04 AM. Reason: typo in narrative

  4. #4
    Registered User
    Join Date
    04-19-2019
    Location
    america
    MS-Off Ver
    2016
    Posts
    4

    Re: Trying to display a specific string if INDIRECT function does not find the Ref

    Thanks Crooza for taking the time to reply.
    I had something like that but I was hoping for something slicker. It's a tool I'm hoping many user will adopt, so user friendliness is important to get people on board.

  5. #5
    Registered User
    Join Date
    04-19-2019
    Location
    america
    MS-Off Ver
    2016
    Posts
    4

    Re: Trying to display a specific string if INDIRECT function does not find the Ref

    agh, I was hoping to not have to delve into VBA. I've been successfully dodging it until now but I guess there's no escaping it here.
    So does that mean I reopen this thread in the VBA forums ?
    Thank XLent you for your help !

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Trying to display a specific string if INDIRECT function does not find the Ref

    No - I will move it there for you.

    Done.
    Last edited by AliGW; 04-24-2019 at 01:39 PM.
    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.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Registered User
    Join Date
    04-19-2019
    Location
    america
    MS-Off Ver
    2016
    Posts
    4

    Re: Trying to display a specific string if INDIRECT function does not find the Ref

    Thanks AliGW !

  8. #8
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: VBA: Trying to display a specific string if INDIRECT function does not find the Ref

    Try this

    The only thing I had to do was change the naming convention of your named ranges and remove the underscore. For some reason this wasn't working in VBA.

+ 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. [SOLVED] VBA Find String before specific character in string
    By Catsonheat in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-25-2019, 07:31 AM
  2. [SOLVED] Can I use FIND function to find specific string in a single cell?
    By bonpara in forum Excel General
    Replies: 2
    Last Post: 10-05-2015, 05:46 PM
  3. [SOLVED] Find and display a specific field
    By DixieDoll11 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-23-2014, 01:06 PM
  4. Help: Find a string then display all value.
    By menash07 in forum Excel General
    Replies: 6
    Last Post: 10-07-2012, 06:07 PM
  5. [SOLVED] how to find string in a cell and display results
    By djc225 in forum Excel General
    Replies: 6
    Last Post: 04-30-2012, 02:35 PM
  6. Find a specific string
    By AK69 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-26-2011, 12:06 PM
  7. Find Specific string
    By janjan_376 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-30-2009, 12:38 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