+ Reply to Thread
Results 1 to 27 of 27

part number data not fully dynamic throughout workbook

  1. #1
    Registered User
    Join Date
    02-20-2017
    Location
    des moines, iowa
    MS-Off Ver
    excel 2013
    Posts
    27

    part number data not fully dynamic throughout workbook

    hello all,
    I have a progress log that tracks the various stages of our part numbers in the certification process. I had asked in a previous thread, http://www.excelforum.com/excel-prog...ml#post4587898, how to populate unique part numbers based on their condition to another sheet. Now, I would very much like to have the entire workbook completely dynamic, if possible. My ideal process would be to enter information into the master, and if that data is changed in the master, then the macro will look for that part number in each of the other sheets, and update them accordingly.
    But here is another tricky part... there will also be some editing on the certification process worksheet, such as marking when certain steps are completed. I would like THIS data to also be completely dynamic with the other sheets.
    I have tried a few formulas in order to make this happen, and they have been working... however, I have already noticed the program running slower and slower, and it will only get worse as I input new data. So I am hoping a macro will help, but I apologize that I don't have one currently to show you.

    I have attached an example of my workbook so that you may better understand what I am trying to do. Any help is appreciated!
    thanks in advance,
    Riley

    P.S. I am not sure how to link a previous post, so i'm sorry if i messed it up!
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: part number data not fully dynamic throughout workbook

    Let me restate the requirements for confirmation and ask some questions for clarification.

    If I add something to the master sheet nothing happens since the part does not exist on the other sheets yet.

    If I change something on the master sheet, I look for the part number on the other sheets and change that information there as well.

    What do I have to change (cells in what column or columns)on the master sheet to copy it to the active, certification or complete sheet?

    What changes (cells in what columns or columns) do I have to change in the other sheets that need to be propagated?
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    02-20-2017
    Location
    des moines, iowa
    MS-Off Ver
    excel 2013
    Posts
    27

    Re: part number data not fully dynamic throughout workbook

    If I add something to the master sheet nothing happens since the part does not exist on the other sheets yet
    that is correct. unless the condition is set to "active", then there shouldn't be any other sheet with that part number.

    If I change something on the master sheet, I look for the part number on the other sheets and change that information there as well.
    yes, for just about half of the master sheet. from about column A to column AQ are going to be managed through the master sheet, and then columns BM through BO as well.

    What do I have to change (cells in what column or columns)on the master sheet to copy it to the active, certification or complete sheet?
    unfortunately, it's just about all up for grabs. about the only column that wont change would be column B. we sometimes have to do various testing and need the most up-to-date measurements

    What changes (cells in what columns or columns) do I have to change in the other sheets that need to be propagated
    as of right now, the only other page that should be manipulated manually (save for the button on the Active Sheet) is the Certification Process sheet. These will be marked off as each stage is completed (columns AR through BL), and then need to be updated throughout the other sheets as well.

    I hope I was able to clear things up and explain it a bit better. I appreciate you taking the time to look into it!

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: part number data not fully dynamic throughout workbook

    Will things be moved off the certification sheet to the completed sheet when all stages are completed?

    What causes things to get moved to the certification sheet?

  5. #5
    Registered User
    Join Date
    02-20-2017
    Location
    des moines, iowa
    MS-Off Ver
    excel 2013
    Posts
    27

    Re: part number data not fully dynamic throughout workbook

    Will things be moved off the certification sheet to the completed sheet when all stages are completed?
    that would be the most ideal, if it's possible. We are just starting this process, but there will eventually be hundreds more part numbers, so i think to help keep everything clean and organized, we would like for them to move to the Complete sheet once all the stages are marked on the Certification Process sheet.

    What causes things to get moved to the certification sheet?
    I'll back track a bit and hope this helps.
    part numbers whose condition is set to active should populate on the Active sheet. The Active sheet is essentially a "waiting list" for the certification process queue. My boss would like to be able to manually arrange the part numbers on the active sheet based on what he considers top priority. these will be at the top of the list.
    on the certification sheet, the bottom list is part numbers on queue for testing. it was decided that there should be a maximum of i think 10 part number in the queue at a time so as to not overwhelm those doing the testing. this is where that button on the Active sheet comes into play. once the active sheet is arranged as needed, pressing the button will transfer those top part numbers into the queue until the queue is full.

    i hope that makes sense...

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: part number data not fully dynamic throughout workbook

    So the boss IDs the top 10 on the active - they stay active until completed and these are copied to the certification sheet. I suppose we'll need a dashboard to keep track of how many is in each status so if 5 of the top 10 are completed it shows up and the boss can send down 5 more to do.

    Do you want fries with that? Oops. I mean do you want dates with all these metrics to know how fast things are moving?

    I have two competing designs in my mind - I have to figure out which one makes more sense.

    And a another question while I have the sheet open. How fast do things move? Hundreds of parts a day or dozens or a few?

  7. #7
    Registered User
    Join Date
    02-20-2017
    Location
    des moines, iowa
    MS-Off Ver
    excel 2013
    Posts
    27

    Re: part number data not fully dynamic throughout workbook

    dflak-
    my thoughts exactly lol!
    i'm not sure how important knowing the dates is, but i'm sure that if i asked, they would probably say yes, they would be important.

    How fast do things move? Hundreds of parts a day or dozens or a few?
    this is a bit hard to determine for us right now. But to date we have over 300 part numbers requested by various customers. i would hope at least dozens a day when we can really get up and running, but i can't say for sure

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: part number data not fully dynamic throughout workbook

    OK - thanks. I think I have enough to go on for now. This is obviously a sizable undertaking, but one within measure to complete. I do ask your patience as I will be working it in the cracks between my "day job" .

  9. #9
    Registered User
    Join Date
    02-20-2017
    Location
    des moines, iowa
    MS-Off Ver
    excel 2013
    Posts
    27

    Re: part number data not fully dynamic throughout workbook

    I appreciate it!

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: part number data not fully dynamic throughout workbook

    I have some questions about the Certification Process Page. I see what appear to be "group" titles like Waiting, Measur, Burst, etc. What are these and how do they fit into the scheme of assignment?

  11. #11
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: part number data not fully dynamic throughout workbook

    Here is my plan of attack.

    Every part number will have a database record with an additional column of Current_Status. This can be maintained in the current master table.

    When a part number is added, the record is added to the database with a current status of “Active.”

    When the part number is assigned to the certification process, the current status becomes “Assigned.” How do I know that the part number has been assigned? What field has to be changed to indicate this?

    When certification is complete the current status becomes “Complete.” How do I know that certification is complete? What field has to be changed to indicate this?” I see a very complicated formula on the Certification Sheet for Status – can you explain what this formula is supposed to do and why it doesn’t exist on the other sheets?

    The sheets for Active, Certification Process and Complete, are merely views of the master sheet based on the record status.

    As changes happen, I can record what was changed and when it was changed.

    I suppose we can also have a status for Obsolete.

  12. #12
    Registered User
    Join Date
    02-20-2017
    Location
    des moines, iowa
    MS-Off Ver
    excel 2013
    Posts
    27

    Re: part number data not fully dynamic throughout workbook

    I see what appear to be "group" titles like Waiting, Measur, Burst, etc. What are these and how do they fit into the scheme of assignment?
    these are because we have multiple people assigned to those stages, and these show which projects they need to be working on.
    How do I know that the part number has been assigned? What field has to be changed to indicate this?
    this is being done by clicking the button on the active page
    How do I know that certification is complete? What field has to be changed to indicate this?”
    once all stages have been checked off as done
    I see a very complicated formula on the Certification Sheet for Status
    this was to make all the sheets dynamic. the certification page was slightly different from the others because it required different cells to be dynamic. It was working, but I think that is why the system is running slow.
    I suppose we can also have a status for Obsolete
    we won't need an obsolete status, just a fail status for those that do not pass certification.

    hope that helps to clear it up! it all sounds so complicated lol

  13. #13
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: part number data not fully dynamic throughout workbook

    Yesterday was a "fun" day around here. I think I have an understanding of the master and active and complete tabs.

    I am still not certain what has to happen for an item to move from active to Certification. I don't see a button on the active sheet. Is it supposed to be that whoever does the assignment selects a row and then clicks the button and that button transfers the entire record. If so, where does it transfer it to? I know it goes on the certification process sheet, but what row?

    I do not have an understanding of the certification process sheet at all. I notice that the same part number can appear on different parts of this sheet. I still don't know what constitutes "all stages have been checked off as done." Does this sheet need all the data, or can it just have columns that indicate which tests have been completed?

  14. #14
    Registered User
    Join Date
    02-20-2017
    Location
    des moines, iowa
    MS-Off Ver
    excel 2013
    Posts
    27

    Re: part number data not fully dynamic throughout workbook

    Sorry I didn't get back to you yesterday, busy day!
    Is it supposed to be that whoever does the assignment selects a row and then clicks the button and that button transfers the entire record. If so, where does it transfer it to? I know it goes on the certification process sheet, but what row?
    that is pretty much it. i'm not sure what their decision process is, but yes the boss would determine which ones to do next, move them to the top of the table, and then click the button to transfer them to the bottom of the certification page (there should be a "header" (merged row with title) that says Queue). the button is supposed to look at a specific range of cells and if an of them are blank the button cuts the top row from the active page and pastes it in the "queue" until the range is full.
    Does this sheet need all the data, or can it just have columns that indicate which tests have been completed
    unfortunately that's what they say they want. and right now most everything is working.. I just can figure out a way to make the workbook dynamic.

    I still don't know what constitutes "all stages have been checked off as done.
    I think as soon as columns AR through BL have content in them (whether it is an "x", check mark, date, or data). or better yet, I think it is when Column BL has content for that part number.

  15. #15
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: part number data not fully dynamic throughout workbook

    Why are there duplicate part numbers on the certification tab and how do you want to handle them?

  16. #16
    Registered User
    Join Date
    02-20-2017
    Location
    des moines, iowa
    MS-Off Ver
    excel 2013
    Posts
    27

    Re: part number data not fully dynamic throughout workbook

    there are duplicate part numbers because one part can undergo multiple testing at a time. which they wanted to keep that way.. and right now i just have a conditional format in place to show which ones are duplicated

  17. #17
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: part number data not fully dynamic throughout workbook

    There is still ambiguity in the QA process.

    I have programming for the Master Sheet that will allow you to add a new record. In this case, it appends the data on the bottom line and also adds it to the active sheet. I also have programming that will allow you to select a record by part number and edit it. Any changes you make to the master will be reflected in the active sheet for the selected part number. You cannot add a blank or duplicate part number.

    I have programming on the Active sheet that will allow you to select a part number and put it in the queue for Certification. The part number still remains on the active sheet, but the status is "Assigned" in both Active and Master Sheets.

    The Certification sheet is "wide open." I have no restrictions on it. The QA people can do whatever they want including making duplicate records. My intention is to make a form to select a part number and move it to the completed sheet. The program will change the status on the Master to Completed. It will remove the record from Active. It will remove the record from QA. It will add the record to the completed sheet. The problem is the "out columns." For example, suppose a part has two records with two different values in Column C. Which one do I use?

  18. #18
    Registered User
    Join Date
    02-20-2017
    Location
    des moines, iowa
    MS-Off Ver
    excel 2013
    Posts
    27

    Re: part number data not fully dynamic throughout workbook

    Why are there duplicate part numbers on the certification tab and how do you want to handle them?
    it's because one part number can undergo multiple tests at a time, by different people. i've been told that they want to keep it that way and just conditionally format those that are duplicated so we know which ones are in multiple testing stages

  19. #19
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: part number data not fully dynamic throughout workbook

    And the answer to my question as to which line takes priority - or do you just want me to copy all of them to the completed tab?

  20. #20
    Registered User
    Join Date
    02-20-2017
    Location
    des moines, iowa
    MS-Off Ver
    excel 2013
    Posts
    27

    Re: part number data not fully dynamic throughout workbook

    i'm not sure what you mean.. sorry

  21. #21
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: part number data not fully dynamic throughout workbook

    If the Certification page has more than one line for a part number, which line do I copy over to the completed sheet, or do I copy them all over?

  22. #22
    Registered User
    Join Date
    02-20-2017
    Location
    des moines, iowa
    MS-Off Ver
    excel 2013
    Posts
    27

    Re: part number data not fully dynamic throughout workbook

    oh well it should all be dynamic and ideally just delete from the certification page and copy over from the master... if that make sense.

  23. #23
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: part number data not fully dynamic throughout workbook

    OK, I think I got it.

    Here is an interim file to give you an idea of where I am going.

    Things are run from a menu. There is a button for this in Cell A1 on each sheet. In general, the menu will call itself after you work with it.

    The Master Sheet and Active sheets are protected. When you select the Add option from the menu, it opens up the last row in the table for editing. You can then enter a complete record. As a minimum, you must enter the part number. The status (Column BN) is automatically set to active. When you click on the add button, the new record is appended to the active sheet also with a status of active. You cannot enter a blank part, nor can you enter a duplicate part.

    There is also and edit function. This opens up the line that contains the part number specified. You can edit this record on the master sheet. When you click the edit button, the record is also copied to the Active sheet, replacing the old record there.

    Then there is an assign to QA function. This gives you a list of all part numbers that are active. When you select a part, the status is changed to “Assigned to QA” on both the Master and the Active sheet and it is put in the last row (queue) of the Certification Sheet.

    There is also a complete function. This gives you a list of parts that are in QA. When you select the part, the status goes to “Complete” in the master sheet, the record is removed from the Active sheet, and at the moment, each of the records that match are copied to the Complete sheet and then deleted from QA.

    I will change this based on your last note. The records will be deleted from the QA sheet, and the corresponding record from the master sheet will be copied to the completed sheet.

    The flow of information goes from the master sheet to the active sheet. I do not have provision to update the active sheet at all, much less have changes made there propagate backwards. All changes must be made on the master sheet.

    The QA sheet is “wide open” the QA people can do whatever they want with this sheet. No information from this sheet is propagated anywhere else. The only purpose of this sheet is as a worksheet for them and to house the part numbers that are being tested (with duplicates if necessary) and these are the only part numbers that are eligible for completion.

    At the moment, the completed sheet isn’t locked, but I might do that.

    The only backwards updates are the statuses: Active, Assigned to QA or Completed.

    I just thought of one more thing. I don't see a need to update completed records in the master file. I can arrange to have these part numbers not show up in the dropdown list. So basically, the dropdown lists on the active sheet and the dropdown list on the master sheet would be the same. The master sheet merely shows more data. The master sheet contains the ability to update the contents of the record, while the active sheet has the ability to assign to QA.
    Attached Files Attached Files

  24. #24
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: part number data not fully dynamic throughout workbook

    Here is the revised version. I hope you can figure out how to work it based on the following instructions.

    Click on the menu button. This brings up the menu form which has the following:
    - Add to Master
    - Edit Master
    - Assign to QA
    - Complete QA
    - Exit

    Add to Master unlocks the master sheet and opens up a row at the bottom. Enter information into all of the columns and then click on the Add Record Button that appears next to the Menu Button.

    The record is added to the Active Sheet Automatically.

    Edit Master does something similar. You get a form that allows you to select a part number. When you click on the edit button, the row with that part number is unlocked and you can edit that row in the table. Click on the Edit Record Button that appears next to the Menu Button.

    The record is also updated in the Active Sheet.

    Assign to QA looks only at the part numbers on the active sheet. When you click on this button, a form appears that allows you to select from a list of active part numbers. Select the part and click assign. The status is updated on both the Master and Active Sheets, and the record is copied to the queue (last row) on the Certification Process sheet.

    Complete QA shows the list of part numbers in QA. Select the part number and click Complete and the status is updated on the Master Sheet, the part number is removed from the active sheet, the part number is removed from wherever it appears on the Certification Process Sheet, and the record is copied from the Master Sheet to the Complete Sheet.

    Exit simply exits the menu form without taking action.
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    02-20-2017
    Location
    des moines, iowa
    MS-Off Ver
    excel 2013
    Posts
    27

    Re: part number data not fully dynamic throughout workbook

    this looks great so far! I will look over it more tonight and tomorrow. Thank you so much!!

  26. #26
    Registered User
    Join Date
    02-20-2017
    Location
    des moines, iowa
    MS-Off Ver
    excel 2013
    Posts
    27

    Re: part number data not fully dynamic throughout workbook

    sorry it has taken so long to reply, but thank you so much for all of your help and hardwork!! the log is up and running and i could NOT have done it without you. thank you so much

  27. #27
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: part number data not fully dynamic throughout workbook

    Glad to be of service. Please mark the thread as solved.

+ 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. [SOLVED] Reference Another Workbook to Grab UPC Based on Part Number
    By Casthenative in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-08-2016, 04:41 PM
  2. VBA not activating workbook fully
    By phil4manu in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-15-2015, 07:11 AM
  3. VBA to fully lock down the workbook after a specified date
    By Sean Gillan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2015, 08:04 PM
  4. [SOLVED] Summarize amount dependend on part number (dynamic)
    By joejonson in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-15-2015, 11:30 AM
  5. Is it possible to fully automate my workbook?
    By ebes69 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-28-2014, 06:17 PM
  6. Copy Data from Closed workbook (bbb.xls) to Open workbook (aaa.xls) (Part 2)
    By alexnkc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2013, 02:06 AM
  7. [SOLVED] Search for a part number in all (max 20) sheets of a workbook
    By Oujous in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-04-2012, 04:03 AM

Tags for this Thread

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