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]

With the workbook I linked:

If you will look on sheet Cable Lists, see the formula notation of OLD / NEW. So the named range Cable_List is taken care of for expansion/reduction. (column G)

Go to the formula in F2 and change the 13 to 50 or 100, a number larger than you would ever have Cable ID entries.
Go to the formula in F2 and do the same. (there are two 13's in that formula).

Now you would just pull down the formula to match the additions in column A.

I assume your copy additions are the range A to E and you may add three or twenty rows of new cable ID's (and associated data).

Does that work for you?

It seems you will want to bring the B:E data over to Contractor A for each item selected in a drop down. Do you have a solution for that?

Howard

 

[njbourque]

Howard,

Sorry I have been sick for a couple of days. Thanks for your assistance it is very appreciated.
I placed a revised version of the spreadsheet here, https://www.dropbox.com/s/ugdomiy8it0ljs4/Test Cable With Contractor Assignment.xlsm?dl=0

Columns B - E data will be brought over later as you indicated, my main goal and focus is to get multiple worksheets listing multiple contractors, a single contractor for each worksheet with each of those worksheets having access and capability of uniquely identifying cables that that contractor is assigned. We are using this example of 14(+ -) cables for simplicity our project workbook has several hundred cables and more than 10 contractors. The VBA you provided with worksheet buttons works great for listing contractor A list of cables. However, when I copy (not sure that is your intent) Contractor A worksheet to a new worksheet named Contractor B I can not uniquely select contractors B cables without duplicates as I can with contractor A. I added a Macro 7() that copies columns F and G over to Column H and I and it changes the formulas in H to match up with the Contractor B worksheet. My method (and it may be wrong) is assigning two new columns for every new Contractor worksheet and have the data validation drop down in that new Contractors worksheet reference the two new columns in worksheet Cable Lists. This way each Contractor worksheet has its own list to chose from and making sure no duplicates are entered in any one Contractors worksheet occurs. The same cable can be listed in multiple Contractor worksheets there just cannot be any duplicates in a single Contractors worksheet.

I was thinking that I will need to create two new columns in Cable Lists worksheet for each contractor worksheet and to create a unique named range to map those two new columns to the new contractor worksheet. My method may be way too complicated and problematic and others like yourself may correct my errored ways. In this workbook, I wanted to use VBA to automate the creation of the contractor worksheet, the unique named ranges, columns for count-if and count index statement/formulas. If there is a scheme that uses only Column A in Cable Lists worksheet with a procedure with an event handler that checks for duplicates in the contractors' worksheet which would work I am open to that method. Right now, the way I was shown to do drop downs that removes previously selected items is this method of count-if and a count index. I am open to any other thoughts to achieve my goals?

 

[L. Howard]

Generally what you want to do, adding columns for additional contractors works, if I understand correctly.

https://www.dropbox.com/s/iyhx69x1o2c28p5/Contractor ADD COLUMNS.xlsm?dl=0

However, there needs to be a distinct named range for the sheet Cable Lists- G, I, K columns, and the formulas must be corrected for the Countif columns. (I & K as the new Named Ranges)

I have renamed three named ranges, Cable_A, Cable_B, Cable_C. (Go to > Formulas tab > Name Manager to check them out)

When col F formulas are moved two columns, A2 becomes C2. Revert it back to A2.
IF(COUNTIF('Contractor A'!$A$2:'Contractor A'!$A$2002,A2)>=1,"",ROW())
IF(COUNTIF('Contractor A'!$A$2:'Contractor A'!$A$2002,C2)>=1,"",ROW())

Also the code that is on the Blue Circle (Create Listing) assigns the named range to that sheets pertinent contractor.
Note in the workbook linked, the code has Cable_C as the named range assignment.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:="=Cable_C"

This presents a problem assigning a correct named range to a contractor. You have to hard code (hand write) the desired named range in the code for each reassignment. I do believe there is a method to do that automatically or perhaps ask the user clicking the Blue Circle to supple the correct named range when the button is clicked.

You say there would be around 10 to maybe 20 max contractors? That's not many named ranges. Once installed on Cable Lists, they are there to use and when a contractor leaves, the named range stays to be assigned to another contractor later. It seems you would construct Cable List sheet like you see it with three contractors, except have 10 or 20 more set up just like Cable A - 13, Cable B - 13, Cable C - 13 headers. I think that would be much better than a macro to attempt to move the columns etc. With that, then we only need to find a way to assign the proper named range in the Blue Circle coding.

Give this some thought and look the linked workbook over, tell me how you think you want to go on.


Howard

 

[njbourque]

Howard,

What a month for me, been outside country working on a project start up. Back in office now for a while. As I have been mulling over this in my head my thoughts were to;
1. create a routine which ask the user to define a contractor using a message box. (con_name)? example
2. assign 'con_name' to a variable that can be assigned to a new worksheet name and a named range on the cable list worksheet.
3. Insert the two columns 'Count-If' & 'Count-Index' with formulas that reference the named range 'con_name' where applicable.
4. as each new contractor is added a new worksheet is created, and the cable lists worksheet is modified by adding two new columns.
things I need to consider are removing a contractor and modifying the name of a contractor

I can do this manually by creating individual worksheets and adding column in ws cable lists but I want to try and automate as much as I can so that I am not spending hours manually editing spreadsheets for each new project. Thank you

 

[L. Howard]

I gave all that some thought and believe it is quite doable. However, code of that nature is above my abilities, sorry to say.

I suggest you start a new thread titled something like "Dynamic Named Ranges with variable columns and sheets and formulas..." or the like.

Howard

 

[njbourque]

I gave all that some thought and believe it is quite doable. However, code of that nature is above my abilities, sorry to say.

I suggest you start a new thread titled something like "Dynamic Named Ranges with variable columns and sheets and formulas..." or the like.

Howard

I will keep trying on and off as my schedule allows. Thank you Howard for the great help.