How can I add a new item to a dynamic validation list without adding to source table

Hi All, hope this is possible and appreciate your help.

I currently have a table example below

Supplier Product
A Apple
A Banana
B Orange
B Grape
B Carrot
C Pepper
C Onion

I have then used OFFSET function in a Named Range (I have called this 'dynamic_list') to generate dynamic Validation list for example when the user selects supplier B in an earlier cell my product validation list only shows Orange, Grape and Carrot.

What I want to do is also add another option to the validation list "Product not listed".

I know this can be achieved by altering the table as follows

Supplier Product
A Apple
A Banana
A Product not listed
B Orange
B Grape
B Product not listed
B Carrot
C Pepper
C Onion
C Product not listed

But when dealing with large number of suppliers it will get messy and may be forgotten when adding a new supplier.

I had hoped I could achieve it by editing the validation source from =dynamic_list to something like =dynamic_list;"Product not listed" so this new option would be added to every list, but it does not work. Any ideas how I can achieve this without having to alter the source table as I have described.

Chris

Hi XLent

Thank you for the response and taking the time time to help me out, unfortunately I could not get it to work, however from my understanding your solution would require user to type in 'Product not listed' rather than it being presented as an option in a drop down list.

I have since come up with another less sophisticated workaround, which actually works well.

Basically I created a dynamic list on a separate sheet where the first entry is a simple INDEX, MATCH based on the supplier selected, subsequent rows continue to populate until the row number exceeds the COUNT of instances the supplier is mentioned. When this limit is reached, I just use IF statement to add 'Product not listed' as final option. I then redirect the named range to look at this list rather than the original source table.