Multiple drop down/data validation lists with one master data range

scurtis32 · May 28, 2013

Hi, first post, thanks in advance for any help coming my way.

Here's my problem I have been unable to solve. I have on one sheet a list of all my company's cost centers (defined in format ###.#### or ###.T where the first three digits relate to sub org reference and the last four as a cost center classification with that org or .T refers to entire org. There are several thousand unique items that make up the entire list).

What I want is on other spreadsheets where I am performing analysis on a per org basis, to be able to have a drop down possible that is filtered for that org. For example, if the org number is 220, the drop down would only show the valid possibilities that begin with "220.". I'm familiar with data validation and using a named range, but using that in this way doesn't work for me. This would be great to share my spreadsheet with others and use it for the orgs they are concerned with without having a lot to change the initial setup and provides a perhaps easier way to manage additions and modifications to the master list of cost centers.

I thought about using a macro to copy a filtered list to a new, hidden worksheet but as I thought through it and realized how the macro would have to check to see if the worksheet and filtered list for that org would have to be maintained and updated, I hoped someone would be able to provide me an easier, more direct route. If anyone has a suggestion on how to approach this, please let me know.

Let me know if I can clarify anything.

Thanks,

Steve

NARAYANK991 · May 28, 2013

Hi Steve ,

I have not understood your data in all its entirety.

Suppose your sub org reference is 220 ; does your master list have numbers from say 220.0004 through 220.7782 , and also 220.T ? If this is so , then when you select 220 , should the DV drop-down show the items from 220.0004 through 220.7782 and also 220.T ?

It would be nice if you could take a specific example of an org reference which has the least number of entries.

Narayan

scurtis32 · May 28, 2013

Yes. The current method is to use a filtered name range (copy and pasted) from the master list for each org set up as a separate worksheet. For org 220, relevant cost centers would be 220.0000, 220.2011, 220.4000, 220.5000, and 220.T . All orgs and related cost centers have a unique structure of org # (###) followed by a ".".

scurtis32 · May 28, 2013

Link to dumbed down test file.

This case is org 221.

See cell B3 for drop down reference. Currently, it refers to a range designed specifically for this org. In order to facilitate easier set up for new users wanting to use my spreadsheet to apply to orgs.cost centers they are concerned about, I'd like a way to reference to the master list of orgs.costcenters (via specific cell in worksheet or use worksheet name).

Thanks.

https://docs.google.com/file/d/0B3HJwfXBcmg1TTFJYzJRMmc4OW8/edit?usp=sharing

NARAYANK991 · May 28, 2013

Hi Steve ,

I am not sure I have understood your requirement fully.

Can you check your file here ?

https://docs.google.com/file/d/0B0KMpuzr3MTVcTlIaGIzRU1jQlk/edit?usp=sharing

Narayan

scurtis32 · May 28, 2013

Sorry - deleted a little too much. Added some back in (included some info you may have already edited).

Refer to sheet "221" and then cell B3. B3 has a data validation drop down list that refers to named range = TwoTwoOneCostCenters, which is in the CostCenters worksheet.

Keep in mind, there will be several worksheets set up the same way, each referring to a unique org. Displayed within the CostCenters worksheet are only two possible orgs with related .costcenters.

I'd like it so that each worksheet is designed to read from one master list of all available cost centers, but only show the ones related to the org in question. So, I can create a worksheet that relates to 221 another one for 225 and both can read from the one list of All Cost Centers and then only show 221. ones for the 221 worksheet and only show 225 ones for the 225 worksheet. Multiply this times how ever many orgs a user may be interested in within my company.

Making sense? Let me know where I can clarify where needed.

Thanks!

SirJB7 · May 28, 2013

Hi, scurtis32!

Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Multiple%20drop%20down_data%20validation%20lists%20with%20one%20master%20data%20range%20%28for%20scurtis32%20at%20chandoo.org%29.xlsm

Worksheets:

1st:

- list of cost centers and ActiveX drop down list box

- named ranges: cell D1 as ParamCostCenter (linked by listbox), cell E1 as ParamCostCenterOrg (first digits)

- dynamic named ranges: CostCenterTable, CostCenterList

2nd:

- data with cost center as attribute

- named ranges: cell G1 as DataValidationCell

- dynamic named ranges: DataTable

There are two implementations:

a) Preset filter for cost center column in 2nd worksheet upon list box selection in 1st worksheet

b) Setup data validation for cell DataValidationCell upon same criteria

Both are handled by the Worksheet_Activate event, this is the code:

-----

[pre]

Code:

Option Explicit

Private Sub Worksheet_Activate()
&#39;
&#39; constants
&#39;  cost center
Const ksWSCostCenter = &#34;Hoja1&#34;
Const kiCCColumn = 1
Const ksCostCenter = &#34;CostCenterList&#34;
Const ksParamCostCenterOrg = &#34;ParamCostCenterOrg&#34;
&#39;  data
Const ksWSData = &#34;Hoja2&#34;
Const ksData = &#34;DataTable&#34;
Const ksDataValidation = &#34;DataValidationCell&#34;
Const ksDataColumn = 1
&#39;  others
Const ksComma = &#34;,&#34;
&#39;
&#39; declarations
Dim rngCC As Range, rngCCO As Range, rngD As Range, rngDV As Range
Dim sCCO() As String, sParamCCO As String, sCCOText As String
Dim I As Long, J As Long, A As String
&#39;
&#39; start
&#39;  ranges
With Worksheets(ksWSCostCenter)
Set rngCC = .Range(ksCostCenter)
Set rngCCO = .Range(ksParamCostCenterOrg)
End With
With Worksheets(ksWSData)
Set rngD = .Range(ksData)
Set rngDV = .Range(ksDataValidation)
End With
&#39;  parameter
sParamCCO = rngCCO.Cells(1, 1).Value & &#34;*&#34;
&#39;
&#39; process
&#39;  extract
With rngCC
J = -1
sCCOText = &#34;&#34;
For I = 1 To .Rows.Count
A = .Cells(I, kiCCColumn).Value
If A Like sParamCCO Then
&#39; array
J = J + 1
ReDim Preserve sCCO(J)
sCCO(J) = A
&#39; string
If Len(sCCOText) &#60;&#62; 0 Then sCCOText = sCCOText & ksComma
sCCOText = sCCOText & A
End If
Next I
End With
&#39;  filter
With rngD
&#39; sCCO as Array(val1,val2,...)
.AutoFilter Field:=ksDataColumn, Criteria1:=sCCO, Operator:=xlFilterValues
End With
&#39;  data validation
With rngDV.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=sCCOText
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = &#34;&#34;
.ErrorTitle = &#34;&#34;
.InputMessage = &#34;&#34;
.ErrorMessage = &#34;&#34;
.ShowInput = True
.ShowError = True
End With
&#39;
&#39; end
Set rngDV = Nothing
Set rngD = Nothing
Set rngCCO = Nothing
Set rngCC = Nothing
&#39;
End Sub

[/pre]
-----

You'd choose both or only one by removing a few lines. Just advise if any issue.

Regards!

scurtis32 · May 28, 2013

SirJB7, thanks for the info. A lot of that is over my head, but I look forward to dissecting it and learning a thing or 50. I appreciate it greatly!

Regards,

Steve

SirJB7 · May 28, 2013

Hi, scurtis32!

I didn't see that you've uploaded a sample file. I just gave it a quick look. Your issue is only regarding B3 cell, or are there more things? As far I could see the worksheet name is the prefix of your center list code?

Please confirm both things.

Regards!

scurtis32 · May 28, 2013

Correct. I'd like the references within B3 to be more robust, so that adding worksheets or changing the org that one is interested in is easier than I currently have it. And yes, the worksheet name (plus the ".") corresponds to the prefix within the master list of orgs and costcenters.

SirJB7 · May 28, 2013

Hi, scurtis32!

Do you think you'd manage to handle the fix or should I upload your updated file?

Regards!

scurtis32 · May 28, 2013

I don't mind handling. Just learning macros and trying to expand on excel skills, so the practice would be good for me.

SirJB7 · May 28, 2013

Hi, scurtis32!

Good! That's the better way...

Just in case...

https://dl.dropboxusercontent.com/u/60558749/Multiple%20drop%20down_data%20validation%20lists%20with%20one%20master%20data%20range%20-%20testForum-1%20%28for%20scurtis32%20at%20chandoo.org%29.xlsm

Regards!

PS: Cheating not allowed, so don't spy.

EDITED

PS2: In my first sample file, when you select a cost center and then go to data worksheet you'll see that filtered values don't respond to the selected cost center. That's because they're randomly created, check the filter definition instead.

PS3: BTW, your center costs begin with a letter, ha!

scurtis32 · May 28, 2013

It does... caught that after I said "###.", but figured the "C" would be easy enough to add. Thanks again, looking forward to learning something out of this!

And thanks for your clarification on this filtered list - I noticed that and didn't know what to make of it, but that explains it.

Steve

SirJB7 · May 28, 2013

Hi, scurtis32!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

Multiple drop down/data validation lists with one master data range

scurtis32

New Member

NARAYANK991

Excel Ninja

scurtis32

New Member

scurtis32

New Member

NARAYANK991

Excel Ninja

scurtis32

New Member

SirJB7

Excel Rōnin

scurtis32

New Member

SirJB7

Excel Rōnin

scurtis32

New Member

SirJB7

Excel Rōnin

scurtis32

New Member

SirJB7

Excel Rōnin

scurtis32

New Member

SirJB7

Excel Rōnin