+ Reply to Thread
Results 1 to 2 of 2

Copying Data into Template based on Criteria

  1. #1
    Registered User
    Join Date
    01-19-2012
    Location
    Pensacola, FL
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    8

    Copying Data into Template based on Criteria

    Good Morning!

    This is may be a two-part question. I'm creating workbook that will help track clock-in and clock-out data for my employees. I have a worksheet named "activity_report" that contains raw data that needs to be sorted through and, based off of criteria, placed into a template that I created on a separate worksheet called "template". Not all of the columns from the "activity_report" will be used. Here's where it gets slightly trickier. Before putting any data into my template, I need as many copies of the template as there are unique values in column A of "activity_report". I need the name of those newly created worksheets/tabs to be based off of those unique values in column A of "activity_report". Let me now further explain the "activity_report" information:

    A1 - User ID (this is the manager for a particular group of people at particular stores)
    B1 - External ID (this is the particular employee under a particular manager. There may be a second record for the External ID that have different clock in and clock out times. this is due to clocking activity based off of a lunch time and final clock out at the end of the day)
    C1 - Store ID (this is the store the particular individual clocked in at)
    D1 - Date (This report is run daily, however, I leave the date information in the raw data in case i need to run other pivot reports--I also use that date to fill in the "date" on the template using a reference formula)
    E1 - Time In (when an employee clocks in)
    F1 - Time Out (when an employee clocks out)
    I1 - Orders Sold (Although it is tied to each employee record, it ultimately gives me the orders sold on the date in question, User ID, and Store ID) <-- This part is not relevant, please ignore, but I left it in the attachment so as to give you visibility as to what i am trying to accomplish.

    So here's the goal: again, I have a template that needs to be copied to x amount of worksheets that is equal to the number of unique values in column A. The name of those new worksheets should represent one of each unique value in column A.

    Once the template copies have been created, it needs to put the name of the tab/worksheet in Cell "A3" of each newly created worksheet.

    I then need on each respective worksheet with template, in range B:B starting on row 10, unique values for what is in "activity_report" column B, that pertain to the same "User ID" that is found on Cell "A3" of the newly created worksheet template.

    With that said, if an external ID is listed twice in the data provided in "activity_report" there are two scenarios: 1st scenario, assuming the external ID is tied to the same store on 2 separate rows, I need the earlier Clock in/clock out data to show up on the respective row according to what is in B:B (row 10 and down) on the created template worksheets in ranges C:C (row 10 and down) for Clock In and D:D (row 10 and down) for Clock Out. the Later Clocking information which would be found on that second listing in "activity_report" would be on ranges E:E and F:F (rows 10 and down). If a second listing does not show up, then ranges E:E and F:F respectively would be left blank. 2nd scenario, if the User ID has 2 different clock in/clock out activities, but tied to a different store, I need the person's name to show up twice on the list, and have the clock in/out information for both times tied to the ranges C:C and D:D respectively for the person listed in B:B for the store listed in A:A (respectively and starting on row 10 and down on the templates for the particular person mentioned in cell A3 of that template.)

    Now that I mentioned the A:A bit, this only depicts the store number tied to the particular record on the created tabs. This info is found in range C:C of the "Activity_report" -- quick reiteration, if a particular individual clocks in and out at two separate stores, i need that particular individual listed twice with the clocking data in ranges C:C and D:D of the templates created.

    I know this is very long winded, but please let me know if this can be done. I have attached an example of the template with an "activity_report" tab for reference. a 3rd worksheet will depict information as i need it on each created tab per user ID named with that particular user ID found on the "activity_report".

    I appreciate everyone's help.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-19-2012
    Location
    Pensacola, FL
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    8

    Re: Copying Data into Template based on Criteria

    call the calvary, i really need some help with this...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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