Data Validated Dependent Lists w/Variable Range Names

Fujirich

Active Member
Joined
May 1, 2003
Messages
320
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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Maybe an example helps:
Book1
ABCDEFG
1ProductNamesMasterProcessorMasterList
2LH-PIFLH-85P
3FDT-425FLH-125P
4LH-PJFLH-85N
5LH-NIFLH-125N
6LH-NI2FLP-85V
7LP-NVFLP-1260
8LP-N3Raptor68
9LP-NNRaptor85
10
11ProcessorCompatibility
12PlateType1234
13LH-PIFLH-85PFLH-125P
14FDT-425FLH-85PFLH-125P
15LH-PJFLH-85PFLH-125P
16LH-NIFLH-85NFLH-125N
17LH-NI2FLH-85NFLH-125N
18LP-NVFLP-85VFLP-1260Raptor68Raptor85
19LP-N3FLP-85VFLP-1260Raptor68Raptor85
20LP-NNFLP-85VFLP-1260Raptor68Raptor85
Sheet1


The users will select the Plate Names from a list on another page, and secondarily, the available processor list has to be compatible with the plate selected. But since the plate product names will change over time, I can't have fixed named ranges for those product names to be used with INDIRECT for the dependent list.

Any ideas?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top