iken
Mechanical
- May 13, 2003
- 151
Hi Folks,
I need a bit of help on using named ranges for data validation lists.
The sample excel file has a snip of what I have. generally the layout cannot change from what I have (not what is in file)
Summary of what I have done so far:
1. I have a list of multiple disciplines, which have multiple companies under each. Cells A1 to A16
2. To get a list for each discipline without multiple companies appearing under each discipline, I have created a helper columns C1, D1, D2, & D3, These are dynamic and will automatically grow/shrink when details are added/deleted from column A
3. In cell H1 to H9 I have a drop down list based on values C1:F1
4. In cells I1 to I9, I would like to have a drop down list that reflects what is under each heading for disciplines (i.e. columns C to F).
This is easily done with named ranges that are fixed, however my ranges in columns C to F will grow or shrink. How can I generate / link the drop down lists in column I?
Currently I have the values in columns C to F named by using the offset formula, and it looks like this is what is throwing things off.
I have generated a list in cells J1 just using Mechanical range that is in cells F14:F15 as a fixed range, and works all good. Think this issue is linked to the way the data is grabbed from column A and shown in Columns C:F
Nay help would be greatly appreciated.
Cheers
I need a bit of help on using named ranges for data validation lists.
The sample excel file has a snip of what I have. generally the layout cannot change from what I have (not what is in file)
Summary of what I have done so far:
1. I have a list of multiple disciplines, which have multiple companies under each. Cells A1 to A16
2. To get a list for each discipline without multiple companies appearing under each discipline, I have created a helper columns C1, D1, D2, & D3, These are dynamic and will automatically grow/shrink when details are added/deleted from column A
3. In cell H1 to H9 I have a drop down list based on values C1:F1
4. In cells I1 to I9, I would like to have a drop down list that reflects what is under each heading for disciplines (i.e. columns C to F).
This is easily done with named ranges that are fixed, however my ranges in columns C to F will grow or shrink. How can I generate / link the drop down lists in column I?
Currently I have the values in columns C to F named by using the offset formula, and it looks like this is what is throwing things off.
I have generated a list in cells J1 just using Mechanical range that is in cells F14:F15 as a fixed range, and works all good. Think this issue is linked to the way the data is grabbed from column A and shown in Columns C:F
Nay help would be greatly appreciated.
Cheers