I've searched on the topic of creating dependent lists to use with data validation, and understand the use of named ranges and the INDIRECT method. My problem is that for my project, the ranges that I would otherwise name and use with the INDIRECT function to create the secondary list will not have fixed names.
As an example: in the 1st column, the names of various products will appear as they are linked to another range that has the master product name list. This list will change over time as new products are added and old ones disappear. These potential changes prompt my problem with using the INDIRECT method for dependent lists: since the product names will change I can't have fixed, named ranges to use for look-up of the data in the columns to the right which need to make up my secondary, dependent list.
Any ideas on how to solve this? Thanks in advance for any help!
As an example: in the 1st column, the names of various products will appear as they are linked to another range that has the master product name list. This list will change over time as new products are added and old ones disappear. These potential changes prompt my problem with using the INDIRECT method for dependent lists: since the product names will change I can't have fixed, named ranges to use for look-up of the data in the columns to the right which need to make up my secondary, dependent list.
Any ideas on how to solve this? Thanks in advance for any help!