+ Reply to Thread
Results 1 to 29 of 29

Multiple Data Validation Lists based on a master list, but same info cannot be selected

  1. #1
    Registered User
    Join Date
    07-02-2013
    Location
    Minot, ND
    MS-Off Ver
    Excel 2010
    Posts
    13

    Multiple Data Validation Lists based on a master list, but same info cannot be selected

    I am looking to make a spread sheet for work.

    Basic concept would be a master list that is a pool of all the tool serial numbers for the particular shop.

    After this there would be a separate sheet for each individual job we have running.

    I would like to have each job number spread sheet have multiple data validations that reference the master list. I know how to set this part up.

    My issue occurs as follows:

    Is it possible/ how would you make it so once a tool serial number is chosen in one drop down list for a job, it can not be selected again for any other job drop down lists.

    Example:


    Master List:
    P123
    P125
    P135
    P139
    P175

    Job #1: (I choose one of the probes from the master list in a data validation list)
    P123

    Job #2: (when I look at the drop down list, it would look as follows)
    P125
    P135
    P139
    P175

    (Since P123 was selected in Job #1, it would not show up anymore in drop down lists since the tool cant be used on 2 jobs at same time)




    And than for instance I plan on leaving a blank choice with the drop down menu as well. So for Job #1 once I select the blank choice of the drop down menu, it would be basically saying that P123 is now back at the shop and available for use on a different job. So than P123 would be included in all the other drop down lists again.

    I hope this makes sense. any help would be appreciated.

    Thanks
    Last edited by nharwell; 07-18-2013 at 11:31 PM.

  2. #2
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    380

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    Hi nharwell

    Is it possible to please post an example sheet of how you want it to look / work.
    If you include an example of how a correct outcome may appear, and where the data is coming from then it is a lot easier for us to work on the coding for you.

    This lets us see how the sheet layout may work, instead of delivering something to you that may be totally different to what you envisage.
    Darren
    Handy things to keep in mind:

    Click *, if my suggestion has helped you
    If your problem is solved, then please mark the thread as SOLVED

    Sharing is Caring .... spread the knowledge

  3. #3
    Registered User
    Join Date
    07-02-2013
    Location
    Minot, ND
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    Ya as soon as I can ill get a general template. Won't b up til end of the weekend tho

  4. #4
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    The following link is to a tutorial which demonstrates how to do it for master list on one sheet and choices on another... but not choices on several other sheets... but it's a start!

    http://www.contextures.com/xlDataVal03.html

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    Why not Minot?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    07-02-2013
    Location
    Minot, ND
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    Ok, another additional question to go along with the initial question. I have attached a simplified version of what I am trying to create.

    The second question is I am trying to create a dependent data validation list based upon the values from TWO other data validations. I have figured out how to use the indirect function to create a dependent data list based upon one data validations but was unsuccessful trying it with two.

    the spread sheet has an example of how I want it to work written out.

    And if we can figure out this issue, than that's where the initial issue of not allowing a secondary selection to come into play. again see spread sheet for the written example.

    I hope you guys can help me out. Let me know if I can help clarify anything any better
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-02-2013
    Location
    Minot, ND
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    Another acceptable solution instead of making it so once an assets S/N has been selected you cant select it on another drop down menu, would be if once you select an asset S/N, that S/N on the master list and/or the drop down list has a fill applied to the cell to indicate the S/N has been utilized

  8. #8
    Spammer
    Join Date
    02-04-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2013
    Posts
    190

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    Something like this

    JP
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-02-2013
    Location
    Minot, ND
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    Yes that seems like it should work. now I just need to modify it to the much larger spread sheet that has 8 columns to choose different tools from and a lot more assets and s/n to choose from. lol.

    I may have a few small questions while attempting to enlarge everything but I think I can figure it out from here.

    Thanks a lot!

  10. #10
    Registered User
    Join Date
    07-02-2013
    Location
    Minot, ND
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    JP,

    I have the formulas set up the same in my main spreadsheet (or seem to) as you did in the simplified spreadsheet you created. The one aspect that doesn't work the same in my bigger version is that if I change the first asset it doesn't clear the info in the second asset choice like yours did? any reason for this.

    secondly, I realized I didn't completely set up the simplified version completely correctly. I have attached both spreadsheets for you to reference. In the full version of my spreadsheet you can select a different tool size for each type of asset up to 8 different S/N per asset type (see spreadsheet).

    I suppose a question I come to now would be would I include an IF statement in each consecutive choice after the first one to account for the different tool size choices? I doubt I am wording this correctly. kind of hard to write outTool Inventory Worksheet.xlsmSimplified Tool Inventory.xlsx

  11. #11
    Spammer
    Join Date
    02-04-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2013
    Posts
    190

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    hi nharwell
    I have to change something in your worksheet, it looks like the first file that i sent you have

    JP
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    07-02-2013
    Location
    Minot, ND
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    alright and thanks. I should be able to work with this, just have to ad more overall rows in the template to account for all the possible assets used but that works.

    one side note. on the template I have a macros button which duplicates the template when you click on it. as it works right now you fill out all the information on the template and based upon what the name is, a new tab is input at the bottom with that name. ideally I would like it so that as long as one of the s/n is still present in either the template, or one of the name based duplicates, that you still cannot select a given s/n that is in use. obviously all the formulas currently work exactly as they should, but once I duplicate the template and it is in a new tab, on the template I can still select an asset that is used. would there be a simple change to make to the formulas to fix this?

    if it is too complex I won't really worry about it and the person I am making this for will just have to pay a lil bit of attention.

  13. #13
    Spammer
    Join Date
    02-04-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2013
    Posts
    190

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    I apologize because i the english language not 100% controlled, so it is not clear to me your question

    JP

  14. #14
    Registered User
    Join Date
    07-02-2013
    Location
    Minot, ND
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    Tool Inventory Worksheet(nHarwell)(jp)(update).xlsthat's fine. ill try to clarify a little bit.

    on the tool inventory spread sheet, under the tab entitled "Template". In the top right corner is a button that says "new job". When this button is clicked it creates a duplicate tab on the bottom of the page based upon the name that is filled out in cell 'Template!B5'

    everything on the template cell and the duplicate cells work pretty close to how I want it.

    What I was wondering is if there is a minor fix to the formulas in the Template and S/n tabs that can be made to alter them.

    That way once you have a duplicate cell made, the assets that are valid in duplicate tab, will not be able to be chosen in the Template tab until they are cleared.

    I attached the spread sheet as an example to better get across my point.

    If this is a difficult or complex solution, please do not worry about it however. this spreadsheet could end up having 15 or 20 jobs at a time that assets are assigned to it. so if the formulas get to complex just let me know.


    Ex.

    I filled out the name in the 'Template' tab, entitle ND-SLW-0060.
    It had 4 DPG-6.5" assets assigned to it (DPG091, DPG156, DPG166, and DPG154) it also has one DPG-8" assigned (DPG 068)
    I clicked on the 'New Job' button (approx. cell is H2)

    upon clicking 'NEW JOB' a new Sheet is created.

    Now if I was to begin filling out the Template sheet for the next "job" that will be needing Assets assigned to it. I would start by choosing which DPG's are assigned to the job.
    Is it possible to make it so that it works across multiple sheets the way it does on the 'Template' sheet. what I mean by this is since there are 5 overall assigned to the new sheet entitled 'ND-SLW-0060.' that until I clear the ND-SLW-0060 sheet of its assets, in the 'Template' sheet you cannot select the 5 already utilized assets?

    and this would have to carry on across as many times as the 'New Job' button has been utilized.

    I feel as if there is not an easy solution or it is Macros and VB based.

    Let me kno if I can be any clearer.

    Thanks

  15. #15
    Spammer
    Join Date
    02-04-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2013
    Posts
    190

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    Hi nharwell
    It is a Data Validation -- Hide Previously Used Items and hide the used items work only on tab Template,
    the formulas in tab Sn column(AE:AO) work only by changes in tab Template. range(A12:K28),and can't registration changes in tab ND-SLW-0060

    Met vriendelijke groeten
    Jean-Paul

  16. #16
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    nharwell,

    This may give you the means to do what you want... but its not complete.

    I've included code which will list all "in use" items on Job sheets, but as is, it is manually updated. I'm not savvy to making macros run on change events (yet), so someone else will have to provide that aspect, in order to update list on each item selected.

    You'll also have to clean up the Data Validation as I noticed some irregularities, which I'm uncertain where they stem from (e.g. on a Job sheet I could select an XTX serial no. in a DTG row, or something to that effect). Probably some other issues need cleaned up to... the file size is starting to get rather large for all the more info it contains.

    Additionally, I suggest you make your Template a blank slate and use it only to create each Job sheet, and making item selections only the Job sheets. My Code for creating the "in use" list starts at sheet index 5 (i.e. ND-SLW-0060), ignoring sheets 1 thru 4 (Assets, Lists, Template, SN) as currently structured... so if you change any of those 4 to a location past sheet index 4 it'll mess up the result.

  17. #17
    Spammer
    Join Date
    02-04-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2013
    Posts
    190

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    Hi nharwell
    Now you can make a new job with the macro and change validations in the new tab with the given name.

    Jean-Paul

  18. #18
    Registered User
    Join Date
    07-02-2013
    Location
    Minot, ND
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    Jean.P28

    I have a couple quick questions.

    Based on the last updated spread sheet you attached.

    First question,

    Am I correct in the assumption the formulas that are on the S/N sheet to the far right side (the ones that originally made it so you could not choose duplicates), are no longer needed since I noticed these formulas are now located on the Template sheet, but were hidden?


    Second question,

    On the Template Sheet, in Cells AA2 and AA4, how did you make it so that these cells display the value of which cell is currently selected.
    Ex. I click on cell Template!A11, and cell Template!AA2 displays the value Template!A11


    Im trying to make sure I understand how everything works for the most part for future endevors.

    Thank you

  19. #19
    Spammer
    Join Date
    02-04-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2013
    Posts
    190

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    nharwell,



    You first question is Yes, all events in the Range ("A11: K24") to have been registered in the columns (aa: ak).
    when in Range ("A11: K24") something selected or changed will be somewhere in columns (aa: ak) also change something to work your template.
    When copying Template tab will then all the codes it copied and autonomously.
    To change those codes and that is the answer to your second question, this event is unfiltered with VBA code behind the worksheet, accessible via Alt + F11, microsoft excel objects, sheet3 (Template) this will also be copied when you press the macro button New job.
    I hope it's a bit obvious, this text was translated by translate
    Regards
    Jean-Paul

  20. #20
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    nharwell,

    Did a little more experimenting, learning...

    Attached file is my simplified version of your objective.
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    07-02-2013
    Location
    Minot, ND
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    jhren,

    yes that is exactly what I would like it to do. I have also simplified everything overall as well, where now you only have to choose the type of asset. no longer need to select asset and size.

    was it all code based to get the spread sheet you made to function as you did?

  22. #22
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    Quote Originally Posted by nharwell View Post
    jhren,

    yes that is exactly what I would like it to do. I have also simplified everything overall as well, where now you only have to choose the type of asset. no longer need to select asset and size.

    was it all code based to get the spread sheet you made to function as you did?
    Not entirely. I used sheet code for the template, which passess on to the "job" sheets when copied from the template sheet. The code uses a change key cells event (i.e. a tool has been chosen) to trigger the module code to update the In Use list. The "Complete Listing" is manually entered, while the "Not In Use Listing" and the "Not In Use CONDENSED Listing" are formula driven. The individual lists (by column header) under "Not In Use CONDENSED Listing" are what is used for data validation.

  23. #23
    Registered User
    Join Date
    07-02-2013
    Location
    Minot, ND
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    jhren,

    OK I almost have it all working as yours does, but im having I suppose two issues. I have attached my spreadsheet so maybe you can figure out what im missing

    I have it so whenever I select a tool from any of the sheets after template that it shows up on the SN sheet under the "InUse" column just like yours did.

    I set up 3 tables just like you did under the SN sheet of my spreadsheet. On the second and third table everything works exactly as it should, only in the first cell of the first columns.
    Ex: When I select DPG091 under any of the Job Sheets, it blanks it out on the "Not in Use table" and in the "Not in Use CONDENSED" table.

    However once I try to drag the formulas over to the rest of the table, the second and third table do not work correctly. I can select an asset and it shows up under the "InUse" list. but once the asset is selected it doesn't "vanish" from tables 2 and 3 like it should.

    Any clues??


    Actual Updated Pathfinder Tool Inventory Worksheet.xlsm

  24. #24
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    nharwell,

    The formulas for the Not in Use and 'condensed' tables are (were*) array formulas.

    =============================================

    * The Not In Use table can be a regular formula. In U3...

    =IF(COUNTIF(InUse,C3)>0,"",IF(C3="","",C3)).

    Copy cell then paste formula to the body of table.

    =============================================

    In AM4...

    =IFERROR(INDEX(U$3:U$53,SMALL(IF(U$3:U$53<>"",ROW(U$3:U$53),1000),ROW(AM1))-2),"")

    Note this is an array formula. Confirm with Ctrl+Shift+Enter and {} will appear around the entire formula.

    Copy cell then paste formula to the body of table.

  25. #25
    Registered User
    Join Date
    07-02-2013
    Location
    Minot, ND
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    That worked perfect thanks. I noticed one interesting side affect I'm trying to figure out. Maybe u could explain.

    I created a couple job forms and filled them out n everything worked fine. So I tried Just deleting a job form n when I just delete the sheet the tools still say they in use. This isn't that big a deal tho. So I restarted and before I deleted the sheet I used the reset form button I created n it removed the assets from the inuse column. However I couldn't select the assets still bc suddenly tge not in use table was reading #N/A in every cell which causes the third table to be entirely blank.

    Could this b the result of my clear contents macro or be due to me hiding the sheet with all the referencing tables so ppl don't accidentally mess with the formula?

  26. #26
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    Can't say for certain your "clear contents" macro is the problem with out seeing the coding... but sounds quite likely.

    A couple suggestions:

    Manual updating: Forget/remove the "clear contents" macro. Create/change button to call the ListInUse macro, and click it after deleting the job sheet (obviously the button cannot be on the deleted job sheet ). This should update the InUse list.

    Auto updating: Create a macro to monitor for deleted sheets, and call the ListInUse when the event occurs. I haven't tried this myself, and as I understand it there is no delete-sheet event, but here's a link...

    http://www.mrexcel.com/forum/excel-q...ete-event.html
    Last edited by jhren; 08-14-2013 at 11:00 AM.

  27. #27
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    PS: If your data validation is setup properly, a blank entry should be permitted for all tool-selection cells. To clear any contents on a job sheet, all you need to do is select the appropriate cells and press Delete key. Those cells should already be monitored, so the ListInUse macro should run and update the InUse list.

  28. #28
    Registered User
    Join Date
    07-02-2013
    Location
    Minot, ND
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    it appears as if the #N/A error is resulting from the InUse formula, but I cannot figure out the fix. In regards to the InUse formula I had just copied what you had in your mini spread sheet and edited to match the correct cells in mine.


    The interesting thing about this issue is that when I initially put in all the formulas everything works. but once I save it and exit out and open it up again things stop working and the #N/A's start appearing... this is just odd.

    If you take a look at the attached spread sheet on the SN sheet, this is where the #N/A is appearing...

    This turned out a lil more complicated than I expected, but I can say I am learning a lot of new things from you guys in trying to learn what it is you are doing to make everything work, and your suggestions on how to fix things

    Pathfinder Tool Inventory Tracker v4.0.xlsm

  29. #29
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Multiple Data Validation Lists based on a master list, but same info cannot be selecte

    #N/A's are a result of the InUse list being empty and using a named range formula to make it size dynamically. I tweaked the named range formula so it is at least one cell in size... and made a few minor changes: Assets Not In Use formula restructured using OR rather than a nested IF; removed blank line at top of "condensed" list (its purpose was to just provide a blank selection, which is available without it).

    On a side note, I've tried several times this go 'round and previously to make the "condensed" named ranges size dynamically, but the data validation drop list don't function when I do nor can I figure out why (I think it may have something to do with the array formula) or how to make it work when the lists are dynamically sized.
    Last edited by jhren; 08-15-2013 at 09:42 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro/VBA to populate information based on date selected from data validation list
    By anonDymous in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-12-2013, 11:03 AM
  2. [SOLVED] Lookup Based on multiple data validation lists
    By Jason Eric in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-02-2013, 08:05 AM
  3. move data from master list to individual lists based on value in 'Unit' cell
    By nforgey in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-28-2013, 07:26 PM
  4. Autopopulate cell based on info selected in Data Validation list
    By mintymike in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-19-2012, 12:42 AM
  5. Replies: 3
    Last Post: 09-02-2010, 03:04 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1