Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts

    copying Data from one sheet to another and counting.

    Hi All,

    I'm wondering if anyone can help me, I have a spreadsheet with a weekly planner on it (please see image). what I want to do is to copy the data from sheet 1 to sheet 2 but to lay it out in a yearly overview. is it possible to have excel read the contents of sheet 1, ascertain the month, day, number of people and task (eg. Bu = Build, Rip = Rip Out... etc), to colour the cell according to the task and to enter the number of people on each specific Job? each day in the yearly will be split into 4, 2 hour blocks, if only one block is filled then all four would be filled the same colour.

    I am not looking for someone to do this for me.. I am looking for help as to where I can start looking to do it for myself.. I want to learn but have no idea where to start or if it can be done.

    Week Planner1.jpg

    thank you for your help,

    kind regards

    Rob

    Edit:

    The yearly planner is so I can analyse times jobs are taking, see where my strengths are and where my weaknesses are in this area.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Rob

    ..looks interesting.
    ..I'll give this some thought and will post my suggestions.
    It would be much easier to help you if you could post a dummy file, rather than a screenshot.
    (This saves us time in re-creating a test file).

    zeddy

  3. #3
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    Thank you for your reply, Yeah I have no problem in posting a dummy file (wasn't sure what would be needed).

    I've added a dummy year planner on Sheet 2 just to show how it would look when finished (in my head) the top line of each month is the projected timescale (filled in manually or ideally from a form that I have still to create), the bottom line is the actual time taken (auto filled from Sheet 1).

    hope this helps

    RobWeekly Planner.xlsx

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Rob

    Thanks for the sample file.
    This makes it easier.
    I fully understand how those numbers got 'posted' (but had to think about it carefully!)

    zeddy
    Last edited by zeddy; 2016-05-05 at 16:41.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Rob

    I have formulas which can exactly replicate your posted values.

    In your sample file, I see that the 'posted' value on [Sheet2] for Fri 6 May, for Gerry Burke, shows a value of 1.00, posted as 'yellow', which corresponds to the two dropdowns of 'Fit' on the Weekly Planner.
    What if one of those Gerry Burke dropdowns was, say, 'Rep'???? The value will still be 1.00, but what about the colour????

    zeddy

  6. #6
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Hi Rob

    I have formulas which can exactly replicate your posted values.

    In your sample file, I see that the 'posted' value on [Sheet2] for Fri 6 May, for Gerry Burke, shows a value of 1.00, posted as 'yellow', which corresponds to the two dropdowns of 'Fit' on the Weekly Planner.
    What if one of those Gerry Burke dropdowns was, say, 'Rep'???? The value will still be 1.00, but what about the colour????

    zeddy
    HI Zeddy,

    I would be interested to know more about your formulas. the values for Gerry Burke being 1 is due to two quarters of a day being selected for 2 people so (0.25+0.25)+(0.25+0.25)= 1, the colour is key to the activities, so rather than have the yearly state "Bu" it simply has the colour meaning I can add to the cell the total hours spent.

    Rob

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Shuggy,

    Here is my VBA solution using the base 3 numbering system for 4 digits to find the time values for the yearly planner. In base 3 from 0000 to 2222 (0-80 base 10), there are 81 different combinations of 0's, 1's, and 2's. Listing all the combinations in numerical order then substituting all the 2's with an "A" provides a 4 digit code. Each digit of the code represents an entry for the two hour block. Here is a portion the code combinations on a hidden sheet showing the decimal equivalent in col A, code string in column B, assigned numerical value in col C:

    Shuggy1.png

    When the code runs, it looks at each employee daily block and builds a code string for each task by assigning a digit to each 2 hour block. In other words, each employee's day will have 5 code strings (one for each task). In you sample, Robin's daily block on Wed May 4th has Kinleith Mill (slot 1), <blank> (slot 2), Repair Machines (slot 3), and Kinleith Mill (slot 4). The code strings Robin's day are:

    Robin: May 4th
    Kinleith Mill: 10A1 = 0.75
    (1 represents the evaluated task in slot 1 (.25), 0 is for the blank 2nd slot (.25), "A" is assigned when a different task is slotted from the one being evaluated (0), 1 represents the evaluated task in the 4th slot (.25)

    Strathaven: 0000 = 0

    Gerry Burke: 0000 = 0

    Repair Machines: 001A = .25

    Meetings: 0000 = 0

    All the values for the tasks (each employee has a value for the task for that day) are added them transferred to the Yearly planner. Any change on the weekly planner are updated as the yearly planner is opened.

    This was a very challenging concept. You can use this concept if you like to complete the project. I also would be very interested in zeddy's approach using formulas. Wracked my brain but couldn't come up with any.

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2016-05-07 at 17:04. Reason: guess I forgot to attach file

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Shuggy

    ..before I post my solution, the colour issue needs to be resolved.
    The colours are defined by the dropdown selected:
    ('Bu'=green; 'Rip'=yellow; 'Fit'=orange; 'Rep'=red)

    As I said in my previous post, I have formulas which give the exact number totals for the yearly overview.
    The numbers are not an issue.
    So let me ask you again:
    We get the value of 1; it is posted as 'yellow' on the yearly overview (because all of the hours in your example are of type 'Fit')
    But, if one of those dropdowns was 'Rep' (instead of 'Fit'), then we would have 0.5 day (red) + 0.5 day (yellow) for cell [DX4]
    ..so, what should the posted colour be if we have that???

    zeddy

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Maud

    ..the formulas are not that complicated - it's the colour requirement I'm having trouble with.

    zeddy

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    I had assumed that the conditional formatting that Shuggy has in place would take care of that

  11. #11
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi all,

    thank you for all of your help and my apologies for the very late reply. I lost my job and all my bookmarks for this site so have been searching through to try and find this again. i'm going to be working on these sheets again at home now though as I will probably still have use for them (and its a project to learn from), so all of your help has been great so far and thank you!

    Zeddy, I had thought of the same problem, the only solution I could come up with was either just copy the weekly planner to create a yearly planner from it as the weeks go by (kinda a record of works) and/or have a second sheet where every day was split into 4 cells and each cell was individually formatted and filled in.. (possibly more work though, and therefore not labour effective).

    Maud, thats a very interesting way of working it, I never thought of creating codes for everything (didn't know it was possible), I'm definitely going to have a play with that and see how things progress.

    once again thank you all and my apologies for the late reply.

    kind regards

    Robin

Posting Permissions

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