DataSwapper

This is an Excel workbook developed to "swap" or copy data between a Master Log/sheet to individual workbooks with the same format and then to copy data from those same individual workbooks back into the Master Log as needed.

LIABILITY WAIVER
Yes, I have to place this here. Although I have tried to design this workbook with care and error checking routines, by using this workbook you release rodericke.com and Roderick Edwards from any and all liability for any fault or malfunction. There is no malicious code contained within this workbook or macro. No tracking or unsuspected access. The code and workbook are all unlocked and ready for the user's inspection.

ZIP FILE
Version 2 ZIP FILE

Such a set up would be useful for instance if your company had a person maintaining a Master Log of orders to be handled by several other individual persons. What this workbook would do would be to allow the maintainer to build the list of orders, assign them to the individual persons and then have those assignments copied to the individual person's workbook for them to process. Further, once the individual person processes the order (adding more data), this program will copy the new process data back into the Master Log. In this way, your company could:

  1. Assign orders to specific individuals for processing
  2. Maintain a Master Log with all orders
  3. Have orders updated on the Master Log from the copying of the individual logs

ASSUMPTIONS/LIMITATIONS

This workbook was designed with some assumptions and it is important that these assumptions fit your needs, so please take a look to make certain your needs meet these design assumptions.

  1. Maintainer is using Excel 2007 or newer
  2. Maintainer can enable macros/content
  3. User understands concept of Unique Identifier, such as a unique PO#. Every row should contain a "UID" of some sort
  4. All of individual data sheets are formatted the same; with the same column headers.
  5. No formulas are involved in the cells being processed, as formulas on these cells will be overwritten if you use this workbook.
  6. All individual workbooks will not be in use while using the Data swapper (Process button).
  7. All individual workbooks are accessible by the maintainer of the Master Log; such as on a share network drive
  8. Individual sheets will overwrite the Master Log; as intended as updater

QUESTIONS

I want to address some questions that might arise...at least I am thinking people would wonder about these questions.

  1. Why not just use a shared workbook?
  2. A: Shared workbooks in Excel are not reliable and may allow for corruption of the overall data by technical or human error.

  3. Why not just use Access which has more reliable sharing functionality?
  4. A: Yes, you could but people tend to be more comfortable with Excel.

  5. Can I give this workbook to other people?
  6. A: Sure, it is open for use by anyone. Just please keep credit back to rodericke.com and understand the liability waiver.

  7. Can I modify the code?
  8. A: Yes, you can do what you want with it. It becomes yours.

  9. Can I contact you with questions?
  10. A: Yes, via the Contact form. I can't promise how quickly I'll reply. But I'll try

DEMONSTRATION

  1. Download the zip file
  2. Extract zip file -- contains 2 Excel workbooks; Master Log and tester.
  3. Save the tester file somewhere on your computer or drive. - tester contains no macro or formula, it is just test data.
  4. Close the tester file
  5. Open the Master Log workbook, enable macros/content
  6. On the Master Log workbook, Configuration sheet, change cell A2 to the exact pathway and name where you saved tester file
  7. Press the Process button on the Master Log sheet
  8. If no errors, you should see a pop-up box with a green textbox indicating process time and other data.
  9. Note changes on the Master Log in column C; this shows what was copied from the Master to the Source (M-S) or visa-versa
  10. Open tester file and see changes, new rows copied from Master Log
  11. Change some data on tester file in columns E10 thru G10 and save file
  12. Close tester file and press Process button again, note pop-up log and changes on Master Log

IMPORTANT: When setting up new individual sheets, make sure the User column (where ever you plan for that to be) has at least one row of data with the user's name. You can remove this row once the data swapping swaps some data onto the individual worksheet.

On the Master Log, you MUST provide the UID in column A and a User name/id in column B. The timestamp will automatically populate when processing.

Note columns C and D on the Configuration sheet. These should be populated with the column letter where the program will expect to see the UID and User name. You need to do this for every file, even if they are the same.

PROCESS

Hopefully this demonstration helps you understand what the macro is doing.
It is:

  1. Checking to make sure all pathways to the individual files are correct
  2. Individual files have the source sheet indicated in column B of the Configuration sheet.
  3. Source files not currently open by another user
  4. Opens individual file for writing
  5. Copies new entries from the Master Log to the individual file that matches the user name listed in column B of the Master Log
  6. New entries are considered by the UID in column A of the Master Log
  7. Copies data from the individual file to the Master Log if the UID is found on both the Master Log and the individual file; assumes not new
  8. Saves and closes individual files

ADVANCED ISSUES

At some point you may want to remove data from the Master Log and save it to another place. Simply delete or cut the entire row as needed.
If the individual file contains a UID and User name not found on the Master Log, it will NOT be copied to the Master Log. This worksheet was designed with the concept that a maintainer will assign orders to the individual files, thus all order creation is done on the Master Log. The individual files are merely for "individuals" to update the data for copying back to the Master Log.
If the sheet name of the individual file is changed, the process will indicate no data was copied from the individual file because the designated sheet name wasn't found on the individual file. See Configuration sheet, column B.

Thanks and I hope this is helpful -- RoderickE

Add new comment

COMMENT POLICY rodericke.com philosophy of transparency, honesty, and liberty allows for guests to make comments without registration or login. Note all comments will be moderated but most legitimate comments will be published even if critical. -- Thanks for commenting - RECENT COMMENTS