Dependent Data Validation dropdown using non-contiguous named ranges

S

shstrating

Board Regular
Joined
Sep 8, 2009
Messages
65
Office Version
  1. 365
Platform
  1. Windows
Environment: Excel 2013 / Win7 Pro – Service Pack 1

I am trying to find a way to get a non-contiguous range of cells to work in a dependent data validation dropdown.

I have set everything up according to the excellent step-by-step at http://www.contextures.com/xlDataVal02.html. My first and second Data Validation dropdowns are working great, but I need to stray from this structure for my 3rd dropdown.

For my 3rd dropdown I want to be able to define multiple named ranges that consist of non-contiguous cells in a Master Product list.

The reason for this quest is that the items in the Master Product list appear in multiple Product Categories and I am hoping I can avoid having to create multiple sub-Master Product lists that contain duplicate items (along with the inherent maintenance issues, opportunities for errors and unsynchronized data that comes with duplication).

I’ve attached some sample data from a much larger data set:

  • Product Hierarchy (Yellow area) shows the relationship from top level Product Category, thru mid-level Product Sub-category, down to Products.
  • Under DV-1, DV-2 & DV-3 (Blue area) are the Data Validation formulas I’m using from Contextures.
  • In ColG (Green area) is the DV-3 Master list consisting of the Product list from ColE with duplicates removed. This is the list I want to create non-contiguous named ranges within.
  • In ColH & ColI (Green area) are the cell references that I need for my non-contiguous named ranges from the DV-3 Master list.
Excel 2012
ABCDEFGHI

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: center"]Product Hierarchy[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9E1F2, align: center"]DV-1[/TD]
[TD="bgcolor: #D9E1F2, align: center"]DV-2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFF2CC, align: center"]Product Category[/TD]
[TD="bgcolor: #FFF2CC"][/TD]
[TD="bgcolor: #FFF2CC, align: center"]Product Sub-category[/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: center"]Products[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9E1F2"]=ProductList[/TD]
[TD="bgcolor: #D9E1F2"]=INDIRECT(VLOOKUP
(P1,ProductLookup,2,0)&"List")[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699"]Admin/Median[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9E1F2, align: center"]DV-3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699"]Desks[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9E1F2"]=INDIRECT(VLOOKUP(Q1,INDIRECT(VLOOKUP
(P1,ProductLookup,2,0)&"Lookup"),2,0)&"List")[/TD]
[TD="bgcolor: #FFCCCC"]What should this formula be so that I can use the non-contiguous ranges below?[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFE699, align: center"]Panels-Based Systems[/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699"]Electrical/Data Components[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFE699, align: center"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699"]Panels[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: center"]DV-3 Master list[/TD]
[TD="bgcolor: #E2EFDA, align: center"]Panels-Based Systems[/TD]
[TD="bgcolor: #E2EFDA, align: center"]Linked Desks & Benching[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFE699, align: center"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699"]Privacy Screens[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]Admin/Median[/TD]
[TD="bgcolor: #E2EFDA, align: center"]G8[/TD]
[TD="bgcolor: #E2EFDA, align: center"]G9[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]Base Assemblies[/TD]
[TD="bgcolor: #E2EFDA, align: center"]G10[/TD]
[TD="bgcolor: #E2EFDA, align: center"]G11[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC"]Base Assemblies[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]Desks[/TD]
[TD="bgcolor: #E2EFDA, align: center"]G11[/TD]
[TD="bgcolor: #E2EFDA, align: center"]G14[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC"]Electrical/Data Components[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]Electrical/Data Components[/TD]
[TD="bgcolor: #E2EFDA, align: center"]G13[/TD]
[TD="bgcolor: #E2EFDA, align: center"]G15[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFF2CC"]Systems[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: center"]Linked Desks & Benching[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC"]Privacy Screens[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]Horizontal Technology Channel[/TD]
[TD="bgcolor: #E2EFDA, align: center"]G14[/TD]
[TD="bgcolor: #E2EFDA, align: center"]G16[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC"]Systems Accessories[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]Panels[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: center"]Horizontal Technology Distribution[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC"]Systems Fabric Board[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]Privacy Screens[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: center"]G10[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: center"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]Systems Accessories[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: center"]G11[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFE699, align: center"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699"]Desks[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]Systems Fabric Board[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: center"]G12[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFE699, align: center"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699"]Electrical/Data Components[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: center"]G13[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFE699, align: center"]Horizontal Technology Distribution[/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699"]Horizontal Technology Channel[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: center"]G14[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699"]Panels[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699"]Privacy Screens[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​
I’m trying to figure out what formula to use in the DV-3 dropdown that will enable me to use the non-contiguous named ranges in ColI thru ColK.

I’ve been searching MrExcel and the web in general with no luck. I think I need to get better at entering well defined search strings for what I’m after.

Any ideas on how to do this within the confines of a single Master Product list (avoiding helper columns, sub-Master Product lists, or VBA of which I know almost nothing)?

Thank you for whatever guidance you can give.

Steve
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Just a gentle nudge in the hope that someone has an alternative to using the INDIRECT function to 'assemble' a non-contiguous range of cells into a list that can be used in a dependent data validation dropdown.

Thanks so much for any help you can give.

Steve
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Using named range with FILTER function in data validation list (dynamic dropdown)
Replies
7
Views
11K
Akuini
Akuini
J
  • Solved
Macro Works for Me but Not Others
Replies
3
Views
2K
Fluff
Fluff
R
  • Question Question
XIRR with non-contiguous data
Replies
5
Views
10K
rwilson200
R
R
  • Question Question
range name using offset formula vs data validation
Replies
3
Views
702
Jonmo1
Jonmo1
R
  • Locked
  • Question Question
how to copy data from a website
Replies
1
Views
1K
Joe4
Joe4

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

Share this page

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