?
Solved

Scroll Monthly Calendar view in same rows

Posted on 2010-01-08
27
Medium Priority
?
310 Views
Last Modified: 2012-05-08
Hi,

Expert has helped me build the attached calendar with the scroll bar. Ive modified Januarys Calendar with names, and I would like to have the same pattern for other months as well. Ive tried to copy Januarys pattern into other months but failed (totally gone). How to replicate Januarys Calendar to other months and the scrolling should remain on the same rows (A2:A19)?  


ID-25030006-Employee-Attandance.xls
0
Comment
Question by:Theva
  • 15
  • 12
27 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 26208463
I also noticed you had this bug can fix it no problem !!! :)
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26208466
Ooops seems to be an other file. I'll look into it if you want
gowflow
0
 

Author Comment

by:Theva
ID: 26208496
yes pls.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 31

Expert Comment

by:gowflow
ID: 26208620
ok pls check it. I'v added 3 blan lines for every month case you have a new employee.
The way it works is that January is where you do the input and it will replicate in all months. If you have a name correction or add an employee you do it in Jan and you will see it in all other months. Do not input names in other months as it is a linked formulas.
Let me know if you like it like this or we can modify
rgds/gowflow
ID-26208620-Employee-Attandance.xls
0
 

Author Comment

by:Theva
ID: 26208691
Hi,

I like your idea to add new employee name, cool. But the scrolling part needs some adjustment. Perhaps, my explanation maybe not clear enough for you to gauge. I've attached the sample calendar view. In this sample when we scroll, the month changed, but the range remain. If possible can we have the same style in Employee Attendance?  
ID-26208691-Sample-Calendar.xls
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26208828
What is the password ? I cannot view the code
gowflow
0
 

Author Comment

by:Theva
ID: 26209067
sorry for that, its rbs
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26209378
Well you are asking something diffrent than what you posted. The file you posted had already the months split and I only adjusted the names. What you are asking here is a totally diffrent issue. The file you posted CRM or the Calendar yes shows the month when you flip the scroll bar however the data that it displays is pulled from an other table. Is that what you want ? Coz the data that you read in the calendar has been inputed via the UserForm1 module that is an input/output module as you can see.
So if this is your objective then will need to build a similar module where you'll input the data and it will be stored in a hidden sheet and when we design the calendar it will then pull the info stored there to show it in the calendar the way probably like you designed names in rows and calendar across.

Is this what you are after ? if yes then I need some input
1) what do you wish to record for every employee ? do you want to record only when theiy are sick/vacation or you want to record also when they are present ?
2) how much the span like calendar from 2000 on ?
3) what info you want to record for the employees ?

This should take couple of days to developp
rgds/gowflow
0
 

Author Comment

by:Theva
ID: 26209840
Hi Gowflow,

No, i don't want you spent so much time for this, ignore it and its shame on me if I request you to do this. I'm so sorry.

I just need one favor from you. I've attached the Calender that you've created for me. I labeled the AL,ML,EL and 1/2day with different color coding. Each color represent 1 day except 1/2 day (0.5). Let say if the user has taken leave (1st name in Jan10), Y=2,G=1,R=1 and P=0.5. So, the AL,ML,EL and 1/2 day columns will show the total leave (days) taken by the user for that month.

The total leaves taken (12 months) by the users are summed at "Summary Table" at end of December's Month (A:266).  

Hope you can help me to code this color with "day" and be able to capture their leave by selecting a specific color.    

Please let me know if my explanation not clear.

 
ID-26209840-Employee-Attandance.xls
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26210054
No problem I actually started to design your calendar but if you don't want then I'll stop.

re your rquest if I understand well you want the total per employee per month of how many days he took reported in the colors and then total at the end ?

1) for Jan-10 KAMARUDDIN, Widya Robetta you expect to have:
in col AG4 the sum of all the yellow here 2
in col AH4 the sum of all the green here 1
in col AI4 the sum of all the red here 1
in col AJ4 the sum of all the violet here 1 (when totalling this will count for 1/2 or you want to see 0.5 ??)
Don't you want also a total column for every staff showing the total like this person would have
in col AK4 the sum for that month 4.5

2) you also want the total of every employee for every month aded and shown in line A266 for that employee and you want:
 a) add the yellow to the violet in B266
 b) show the green under E266
 c) show the red (but you colored it green) under H268
Iguess you are inputing allocation and Balance AL right ??

Pls confirm my reasonning is correct and case yes I strongly suggest we creat a Yearly sheet where we plug the totals and not at the end way down where it is very confusing

let me know
gowflow
0
 

Author Comment

by:Theva
ID: 26210238
Hi gowflow,

Please ignore my sample calender, I don't want you spent your precious time for that when we have other approach.

You've decoded my mine perfectly with request on color coding.

>Don't you want also a total column for every staff showing the total like this person would have
in col AK4 the sum for that month 4.5.
*Sounds good, pls add this.

> Iguess you are inputing allocation and Balance AL right ??
   *Yes, I'll update this column
> c) show the red (but you colored it green) under H268
   *Pls change it to red (my clumsy work)

> Pls confirm my reasoning is correct and case yes I strongly suggest we creat a Yearly sheet where we plug the totals and not at the end way down where it is very confusing
* I agree with you, please proceed.

 
0
 

Author Comment

by:Theva
ID: 26210311
Hi,

This was slipped:

>in col AJ4 the sum of all the violet here 1 (when totalling this will count for 1/2 or you want to see 0.5 ??)
*Better put it as "1/2", if the user for some reason has taken 3 half days in a month than the total will be 11/2 days.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26210484
ok fine except for the 1/2 I'll see what  I can do as by putting 1/2 then I am converting to string whcih is mmmm will see !!!
Let me work on it I am adjusting your format to have everything aligned in proper columns also I am adding in the first column ID will number your employees from 1 to ... how many you want it is easier to locate a number than to locate a name and then you can change the name wihtout it affecting results. Say a lady get married now her name becomes Mrs xyz name of the husband but remains employee 10

Sorry got to rush out am going out tonite will work on it later on
see you
gowflow
0
 

Author Comment

by:Theva
ID: 26210607
Hi,

If 1/2 day give give you problem then you can retain it as 0.5. As long as we know that's referring to 1/2 day. For the numbering we start with 1. For the naming part I rather leave it to expert's hands. If you think that will provide more flexibility in change management, please do so.

OK, enjoy yourself. Do let me know if you need further info, and don't forget to e-mail me your postal address. Don't think I'm trying to "ice" you. As I mentioned earlier, points are not sufficient for your willingness.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26211465
Don't worry points are more than enough. If you need more ellaborate solutions then I am open and ready to do professional work basis an agreement. What you ask is not worth any remuneration and is my pleasure to give it to you free !!!
rgds/gowflow
0
 

Author Comment

by:Theva
ID: 26211549

Thanks for your kindness gowflow.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26220169
Well pls take a look at this file and let me know your comments. Here is how it works
1) In January all names and Id can be inputed only (column A, B) afterward all other months are automatically duplicated from January.
2) When a day off is colored (Yellow, Green, Red or Pink) once the cursor is moved from the cell the total appears on the item colored. Likewhyse is any mistake done then removing the color will recalculate.
3) Once the Focus is set to Yearly sheet, it calculate all staff's days off and report them on the sheet grouping Yellow + Pink under Yellow, Green, Red.
4) Total allocation on col F can be inputed and col G calculates the diffrence between Allocation and the total used.

Pls check if all ok and let me know.

If you want I thought maybe it would be intresting for you to haave an other sheet called Monthly and it would recap per staff every month so you can have a view of the year in 1 sheet. Simple to do just let me know if you want it.

Rgds/gowflow
ID-26220169-Employee-Attandance.xls
0
 

Author Comment

by:Theva
ID: 26222717
WOW! I have no words to describe this, for you this could be just another creation, but for me this is an elegant stuff!

> If you want I thought maybe it would be interesting for you to haave an other sheet called Monthly and it would recap per staff every month so you can have a view of the year in 1 sheet. Simple to do just let me know if you want it.
*Yes, please.  
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26274061
Hi again,
I modified it sligtly I included Monthly and in Yearly I made a break down between 1/2 and AL. See if you like it like this. I added also totals so you can always veerify integrity of both Year and month to match. One more thing I added also Last Year Cumulative as don't know if it applies in your country but here staff can drag vacations for 2 years back so they can still have vac not taken from previous year.
Basically you input figures in Col G for this year allocation and all the balance of previous year is filled in Col H. If you don't hv this just ignore this col don't fill anything.

Hope you like it and we can still automoate a lot if you want like a front end to input instead of inputing in the tables and also automate the calendar layout ... anyeay appreciate your comments.
rgds/gowflow
ID-26274061-Employee-Attandance.xls
0
 

Author Comment

by:Theva
ID: 26275764
Hi,

What you've created to me is already beyond with your brilliant touch. We also practicing "leave carry forward" and the column allocation for this really rhymes.

>we can still automoate a lot if you want like a front end to input instead of inputing in the tables and also automate the calendar layout.
* I like the idea of inputing information at front end rather than directly entering in the table. This can prevent mistakes. I'm not sure how the automation of calender layout look like,like to see one. However, if you think you have sufficient time to build this,then please do so.  
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 26276530
Well for the second part it is definitvely not in the scope of this question. If you want we can close this one and put a new question and call it the same as this one with part2 refering to this one and state exactly what you want to aim at.

Did you try this one is all ok ?
rgds/gowflow
0
 

Author Comment

by:Theva
ID: 26276859
Hi,

Its not ok, its SUPERB!
0
 

Author Closing Comment

by:Theva
ID: 31674467
Thanks for your help and ideas.
0
 

Author Comment

by:Theva
ID: 26277617
Hi gowflow,

I've posted Part-2 Q,here's the ID: 25042225. Please check this if you have time.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 26278245
Your welcomed Theva. I suggest for next time that you accept as solution the comment that is the most obvious that helped your solution to the question. In this case it should hv been ID 26274061.
This will help people looking for similar solutions either here or on the net.
rgds/gowflow
0
 

Author Comment

by:Theva
ID: 26346156
Hi,

Well noted, I wont make this type of mistake again. Sorry.
0
 

Author Comment

by:Theva
ID: 26429022
Hi,

If you have time please look at my Q ID: 25094426.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question