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 !
Bookmarks