×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Data Validation Lists Using Dynamic Name Range

Data Validation Lists Using Dynamic Name Range

Data Validation Lists Using Dynamic Name Range

(OP)
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

RE: Data Validation Lists Using Dynamic Name Range

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Data Validation Lists Using Dynamic Name Range

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

RE: Data Validation Lists Using Dynamic Name Range

@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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Data Validation Lists Using Dynamic Name Range

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

RE: Data Validation Lists Using Dynamic Name Range

(OP)
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

RE: Data Validation Lists Using Dynamic Name Range

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.


Trade         CompanyName...
Architectural A Company 1
Architectural A Company 2
Structural    S Company 1
Structural    S Company 2
 

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close