Creating multiple worksheets with data validation based on a single master worksheet

[njbourque]

Hello everyone its been a while since I asked for help but I am trying to create a dynamic list, maybe I should use a table, on one worksheet (something that can grow or shrink in # of rows over time) and have that dynamic list available on several other worksheets in dropdowns so that when an item is selected using the dropdown on the other worksheets that particular item is removed from the list but is still available on other worksheet dropdowns until it has been selected in that other worksheet.
Example;
Worksheet A has a master list of electrical cables (Range A1:A?). This list needs is adjusted as the project adds or removes (dynamic) cables as needed.
Worksheet B is a cable list to be assigned a contractor to supply, Column A in worksheet B has drop downs in cells A2- A?? where A1 is header , A2 is beginning of data validation drop downs and A?? would be the last cell of data validation dropdowns based on the range of rows in worksheet A (range A:A?). In worksheet B my data validation drop down will show all the electrical cables from the list in worksheet A with no blanks. Because the list is dynamic I want my other worksheet dropdowns to match. When in worksheet B an electrical cable is chosen from the drop down list the next drop down selection shall have that previous selected cable removed. I do not want duplicates in worksheet B's list. If I remove the item from my list in worksheet B I would like to have that cable added back to my selection for worksheet B.
Worksheet C is to be a duplicate of Worksheet B in functionality, ie the dropdown list is again from data validation drop down in column A of Worksheet A with the number of dropdowns equal to the dynamic range in Worksheet A (A:A?), and when items are selected the next selected drop down will have that previously selected item removed or added back if deleted from worksheet C.
I would like to create as many new worksheets as possible and develop unique lists for each worksheet without having duplicates on that unique worksheet. Cables can be duplicate from one worksheet to another just not duplicated on a single worksheet.
My thought is to create, on Worksheet A, my electrical cable list using COUNTIF formulas to provide a separate validation list for each new worksheet B, C, D etc. these would be on adjacent columns in sheet A . I am not sure if this is the best solution any ideas would be greatly appreciated.

 

[L. Howard]

I think I understand your scheme.

My test workbook has three sheets on it.
Contractor A
Contractor B
Cable Lists

Contractor A & B sheets have a series of drop downs, say about 15, in column B, row 2 and down. (can be any column)
Cable Lists sheet has columns A, B, C for Contractor A and columns E, F, G for Contractor B. (repeat the three column pattern for more contractors)
There are two dynamic named ranges, Cable_1 and Cable_2

On Contractor A sheet, you pick any drop down and make a selection, now that selection is no longer available to any of the other drop downs on Contractor A sheet. Pick enough selections from the other drop downs and the list is exhausted and the drop downs 'just do not work", they show nothing.

Meanwhile over on Contractor B sheet, there are no selections made and all cable options are available. You do the selections on B sheet as described on A and the list of available cable options are selectable only once.

To fully restore the entire cable option back to sheet Cable Lists, you select all the drop down cell on a contractor sheet and hit delete. Or select only individual cable options to delete and return to availiability. Ready to start the selection process again.

Is that pretty much your scheme, (perhaps with many more contractors than the two I am using)

Howard

 

[njbourque]

I think I understand your scheme.

My test workbook has three sheets on it.
Contractor A
Contractor B
Cable Lists

Contractor A & B sheets have a series of drop downs, say about 15, in column B, row 2 and down. (can be any column)
Cable Lists sheet has columns A, B, C for Contractor A and columns E, F, G for Contractor B. (repeat the three column pattern for more contractors)
There are two dynamic named ranges, Cable_1 and Cable_2

On Contractor A sheet, you pick any drop down and make a selection, now that selection is no longer available to any of the other drop downs on Contractor A sheet. Pick enough selections from the other drop downs and the list is exhausted and the drop downs 'just do not work", they show nothing.

Meanwhile over on Contractor B sheet, there are no selections made and all cable options are available. You do the selections on B sheet as described on A and the list of available cable options are selectable only once.

To fully restore the entire cable option back to sheet Cable Lists, you select all the drop down cell on a contractor sheet and hit delete. Or select only individual cable options to delete and return to availiability. Ready to start the selection process again.

Is that pretty much your scheme, (perhaps with many more contractors than the two I am using)

Howard

Howard, Thank you for the quick reply, Happy Holidays.

Your first attempt at describing what I have in mind is, wow, really close (95% accurate).

Description of Cable Lists worksheet is correct in theory, I want to start with 7 columns in Cable Lists worksheet, Columns A,B,C,D,E contain cable information and qty's then column F and G hold my "COUNTIF" and "COUNT-INDEX" formulas respectively. As a new contractor worksheet is added I want the Cable Lists worksheet to add the 2 new columns after G (now H and I) which will be used for that new contractor worksheet. This I am debating VBA code to handle (both create and remove contractor worksheet and associated columns in Cable Lists worksheet).

The dropdowns in the contractors worksheet I want to be based on the number of cables listed in the Cable Lists worksheet not a fixed number. This is where I get stumped and wonder if my approach is incorrect.

The rest you nailed it. I need to read up on how to provide copy of what I have so far for Calbe Lists worksheet I will provide in subsequent post.

Thank you again for assistance, much appreciated.

 

[L. Howard]

Okay, you cannot post attachments here but you can post a link to your example workbook. Use one of the link utilities like Drop Box.


Howard

 

[njbourque]

Hoping this works for you Howard,

https://www.dropbox.com/s/6pcrb3vxse8akqk/Contractor Test Cable Schedule.xlsx?dl=0

on sheet 2 I have copied around 30 drop down boxes in cells A1 - A30 just to test the functionality of what I want accomplished.

 

[L. Howard]

I have the workbook, taking a look.

Howard

 

[L. Howard]

Haven't forgot you, hung up on a small bit of code to do the drop downs on Contractor sheets.

Howard

 

[L. Howard]

Give this a try.

Click the Blue circle shape to install the drop downs, there will be as many drop downs as the formula in AA1 shows, using an out of the way cell for this. (sheet Cable list).
The cells with drop down are colored in a light shade.
Make your selections from the drop downs until done.

When done and ready to clear all and start again, click the red circle/slash to clear all.

Here is a link to the sheet you posted, ready to test.

https://www.dropbox.com/s/3vnkcgvjhxyubld/Contractor Test Cable Schedule EXP njbourque.xlsm?dl=0


Howard

This code goes in the Contractor A sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$A$2:$A$30")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
Dim cOp As Long

cOp = Sheets("Cable Lists").Range("AA1") 'refers to a formula in AA1 that counts the named range remaining items

Sheets("Contractor A").Range("A1") = cOp & " - Cable Options"

Target.Offset(1, 0).Select

End Sub

These codes goes in the a Standard Module.

Sub Cont_A_DDown()
Dim myCheck
Dim rMain As Long

rMain = Sheets("Cable Lists").Range("AA1")

Sheets("Contractor A").Range("A1") = rMain & " Cable Options"

 With Range("A2").Resize(rMain, 1).Validation

    myCheck = MsgBox("Add " & rMain & " Drop Downs?", vbQuestion + vbYesNo)
    If myCheck = vbYes Then
    
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:="=Cable_List"
    Else
        MsgBox "No, exit."
        Exit Sub
    End If

 End With

 With Range("A2").Resize(rMain, 1).Interior
   .ColorIndex = 19 '35 '24
 End With

End Sub



Sub A_Delete_Reset()
Dim lRow As Long
lRow = Cells(Rows.Count, "A").End(xlUp).Row

  Range("A:A").Interior.Color = xlNone

  With Sheets("Contractor A").Range("A2:A" & lRow)
    .ClearContents
  End With

  With Range("A2").Resize(lRow, 1).Validation
    .Delete
  End With

  [A2].Activate
  [A1] = "Cable Options"

End Sub

 

[njbourque]

Thank you Howard,

Pls give me a couple of days (weekend) to check it out.

Joe

 

[njbourque]

Howard,

Thank you for the coding and the spreadsheet. You provided a good start to a working scheme. I was initially thinking of writing code to copy columns “F” & “G” in ‘Cable Lists’ for each contractor worksheet created (that was getting messy) and your code removes that, which is great, thank you.

In my initial post I described my dilemma and whether I should use a table or a dynamic range on ‘Cable Lists’. The reason being is that as my project moves forward in time, ‘Cable Lists’ has items (cables) that are added to and removed from continually it does not become static until we are well into construction. The ‘Contractor A, B ,C ‘ worksheet drop downs I was trying to make so that it reflected those changes in ‘Cable Lists’ dynamically so I could add and remove without re-validating dropdowns. Looking at my formulas in ‘Cable List’ columns "F" and "G" I hard coded the formulas which makes it difficult to copy and insert new rows on ‘Cable Lists’ and have the ‘Contractor ‘ worksheets reflect those changes in their drop downs. I could use event handling code to update the ‘Contractor ‘ worksheets when ‘Cable Lists’ is modified but fear that I will need to re-enter all cables from before, and I am not sure this is the best method to do this task. I also don’t know how to edit formula in column “G” of ‘Cable Lists’ so that when it is copied down it provides subsequent range access needed.

Your help is greatly appreciated and I apologize if I was not clear in my first post.