Time Model Graph

[cakonopka]

Hi All

Got a bit of a job on and want to set up a macro or Program to do the following. I have a sheet with a table of peoples hours they are doing during the week each day. I want the hours to be put into a model shading the appropriate times to show when people will be in. I have attached a sample sheet of what i want it to do which i have had to do my self. Sheet 1 has table in and sheet 2 has the model i want to be automatically filled in once ihave entered all my data in the original table.


Thanks

Chris

p.s. any ideas on how i can do this would be great.

[avveerkar]

Hi All

Got a bit of a job on and want to set up a macro or Program to do the following. I have a sheet with a table of peoples hours they are doing during the week each day. I want the hours to be put into a model shading the appropriate times to show when people will be in. I have attached a sample sheet of what i want it to do which i have had to do my self. Sheet 1 has table in and sheet 2 has the model i want to be automatically filled in once ihave entered all my data in the original table.


Thanks

Chris

p.s. any ideas on how i can do this would be great.

Paul has no graph because his start time and finish time are not same every day. So no graph if not same start or stop time on every day of the week right?

What happens if start time is say 19:00 of one day and say 02:00 hours next day. How do you then want to show the graph?

A V Veekar

[avveerkar]

I had created a similar sheet which is attached. You could modify it a little to meet your requirements. Else I can modify and mail it to you. Please let me know.

Before I can modify you need to answer those two queries I had asked.

A V Veerkar

[cakonopka]

A V Veerkar

That is just brilliant. That is exactly what i am looking for, Just perfect. Ill have a little go at editing it my self. But that is just so great, im so happy, didnt expect anyone to be able to help. Thankyou ever so much. I have decent excel skills but i had no idea how to set a control button or macro to do the graph part, is wicked. Ill have a play and let you know how it goes, might need a lil more help soon.

Thanks so much Again

Chris

[cakonopka]

Thought id ask a few questions before i start playing. Can i just change the date boxes to time boxes such as 24hr time and will it plot automatically for me if i enter times?

Cheers

Chris

[avveerkar]

Thought id ask a few questions before i start playing. Can i just change the date boxes to time boxes such as 24hr time and will it plot automatically for me if i enter times?

Cheers

Chris

Yes it will work. The macro does not care what data you have for row headers or column headers. It just looks for a match. does not matter if it s date, or time or just any number. It it finds a match it would draw a plot. Just that start value should be smaller than finish value.

A V Veerkar

[cakonopka]

Hi, all going well, just wandering what parts of the code do i have to change so that i can add a column in at the left. So start time is column C rathe than B.

Code Below:

Please Login or Register  to view this content.

[Leith Ross]

Hello Cakonopka,

The attached workbook is your original workbook with macros added and with "Weekly Planner" formatted with daily graphs. I see you have been working hard with A V Veerkar on this project, and I don't want to disrupt your working relationship. Have a look , it may help you out.

Sincerely,
Leith Ross

[avveerkar]

Hello Cakonopka,

The attached workbook is your original workbook with macros added and with "Weekly Planner" formatted with daily graphs. I see you have been working hard with A V Veerkar on this project, and I don't want to disrupt your working relationship. Have a look , it may help you out.

Sincerely,
Leith Ross

Superb Leith! That is the way to go!! You really rock!

Cakonopka, did you have a look at what Leith has done? So elegant! So superb! So complete!

A V Veerkar

[cakonopka]

What can i say. You are both excellent people. That is exactly what i wanted. Am so geratful. Its great to have forums like this where one persons problem can be solved by someone else helpin. Is great to exchange such knowledge with each other. Thankyou so much. Ill pop back prob tommorow if there are any changes i need to make if i get stuck, lol.

Thanks both again, has been soooo helpful, i also seem to be havin a lil bette knowledge of coding now so might have an experiment.

Many Thanks

Chris

[cakonopka]

Hi Leith

Just wanted to know how did u get the top row on the page to stay at the top even when u scroll down the sheet?

Cheers

Chris

[avveerkar]

Hi Leith

Just wanted to know how did u get the top row on the page to stay at the top even when u scroll down the sheet?

Cheers

Chris

To freeze first three rows (so that they don't move up and out of the window), select row 4 and then on menu bar click Window and then tick freeze panes. Unfreezing will scroll the rows out.

A V Veerkar