Results 1 to 8 of 8

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

Threaded View

  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

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