Data Validation Lists Using Dynamic Name Range

[iken]

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

 

[SkipVought]

Hi,

The OFFSET() function should work with this caveat: there can be nothing in the column below the data that is contiguous with Row 1. So you cannot have two Mechanical lists in column F.

=OFFSET(C1,1,0,COUNTA(C:C)-1,1)

I generally don't use the Offset() function.

I prefer Named Ranges, named via the name in the first rows of the list, that is there cannot be duplicate names.

I put these lists on a separate sheet having a WorksheetChange Event procedure that will recalculate a range whenever a Change occurs on the sheet in the column for that list.

I can see that your ranges are fixed and not dynamic as opposed to the formula I posted above. On your sheet I can't figure out what you're trying to accomplish overall. I understand that you want dropdowns in I based on the Selection in H, but beyond that, what is your goal?

BTW, I'm on my iPad with no access to Excel until I get back to my office.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dik]

Fixed ranges go good with named ranges...

Rather than think climate change and the corona virus as science, think of it as the wrath of God. Do you feel any better?

-Dik

 

[SkipVought]

@dik how's that?

The OP has fixed ranges with that majority of each range empty???

Unless you have a crystal ball, in use you might have more entries that the fixed range.

I'd venture to assert that dynamic named ranges are overwhelmingly better than fixed ranges!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dik]

Sorry, for fixed data... I often make reference to a named range in my spreadsheets.

Rather than think climate change and the corona virus as science, think of it as the wrath of God. Do you feel any better?

-Dik

 

[iken]

Cheers guys,
The spreadsheet I attached is just a portion of the actual file.
A bit more words around what I am trying to achieve.

Column A represents trades and all companies we use under each, so there could be some 20 odd under each trade/discipline.
Column C, D, E & F are my helper columns. These take from the list in column A, and under each trade/discipline they sort and remove duplicates of company names. The second Mechanical list (Mechanical_2) starting in F13 is just to show this works with a fixed name range, and when the list is not generated from the formulae used in columns C, D & E.
Column H represents (or will) a drop down list for trades/disciplines. Easy enough.
Column I - this is what I can't get to work. When someone selects Arhitectural in say H1, then the drop down list in I1 should just show Architectural companies. This is the same for all cells H1 to H9. There might be more than 1 Architectural selected in this column. Currently this list is blank, and I think this relates to the formulae used to grab this data from column A

The drop down list in J1 is just there to show I can link this to the trade in F13, and then only shows those companies below J13

I did notice I have linked the drop down list in I1 to civil, rather than values in column H, so I have corrected this in full.

Cheers

 

[SkipVought]

You need to take a course in database design or do some study on your own.

Your table would be more useful if it had at least 2 columns like.

[pre]
Trade CompanyName...
Architectural A Company 1
Architectural A Company 2
Structural S Company 1
Structural S Company 2
[/pre]

Why enter values in what you're calling Helper Columns, when you can enter them directly in a Table? You can SORT and/or FILTER by Trade. Your Column A structure is awkward and not at all conducive to Excel analytic tools.

Making your Table a Structured Table will enhance its capabilities significantly.

I see no utility to Drop Downs in this application.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]