Results 1 to 11 of 11

20160504, 16:04 #1
 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.

20160504, 16:11 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,172
 Thanks
 151
 Thanked 587 Times in 559 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 recreating a test file).
zeddy

20160505, 14:13 #3
 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

20160505, 15:20 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,172
 Thanks
 151
 Thanked 587 Times in 559 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!)
zeddyLast edited by zeddy; 20160505 at 15:41.

20160505, 16:12 #5
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,172
 Thanks
 151
 Thanked 587 Times in 559 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

20160506, 15:43 #6
 Join Date
 Apr 2016
 Posts
 11
 Thanks
 3
 Thanked 0 Times in 0 Posts
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

20160507, 14:43 #7
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,754
 Thanks
 129
 Thanked 693 Times in 629 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 (080 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,
MaudLast edited by Maudibe; 20160507 at 16:04. Reason: guess I forgot to attach file

20160507, 15:25 #8
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,172
 Thanks
 151
 Thanked 587 Times in 559 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

20160507, 15:26 #9
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,172
 Thanks
 151
 Thanked 587 Times in 559 Posts
Hi Maud
..the formulas are not that complicated  it's the colour requirement I'm having trouble with.
zeddy

20160507, 16:10 #10
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,754
 Thanks
 129
 Thanked 693 Times in 629 Posts
I had assumed that the conditional formatting that Shuggy has in place would take care of that

20160606, 09:17 #11
 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