Excel formulas for entering data in one sheet then populating that data into two other sheets.
I need help with simple excel formulas, I have 5 fields for data that need to be filled into 2 sheets in the same workbook.
It would take me months to figure out the formulas, I need help with 3 formulas.
Here is a description of what I am having trouble with creating.
DashBoard to Chart
(Formula #1) Name entered in dashboard
When name is entered into script it should FIND name (not ref to cell #) in list on Chart and put the data into that row for that person
(Formula #2) Vacation Month and Day entered in dashboard
In name row in (Chart) Month column, number of hours should go there (we should be able to add additional hours to this cell which would add to existing value) ( we should also be able to change month and day, incase employee changes the date for booked vacation)
DashBoard to (Calendar Month)
(formula #3) Type of Time off (code) BOT or VAC or SAF (example…. Chris P – BOT)
Vacation Code next to name in Calendar in sheet 3 (example…. Chris P – BOT)
The only thing in the calendar will be the employee name in the first open cell in the calendar box for the date requested with the three digit (Code) after.
On calendar, there are 6 cells for each day, if first cell is occupied then put next name entered into next available cell under it.
I hope that kind of makes sense.
I have the Workbook designed with all the sheets ready to go, I just need help with the formulas.
Thanks so much.
Chris.
For-Submission.xlsx
It would take me months to figure out the formulas, I need help with 3 formulas.
Here is a description of what I am having trouble with creating.
DashBoard to Chart
(Formula #1) Name entered in dashboard
When name is entered into script it should FIND name (not ref to cell #) in list on Chart and put the data into that row for that person
(Formula #2) Vacation Month and Day entered in dashboard
In name row in (Chart) Month column, number of hours should go there (we should be able to add additional hours to this cell which would add to existing value) ( we should also be able to change month and day, incase employee changes the date for booked vacation)
DashBoard to (Calendar Month)
(formula #3) Type of Time off (code) BOT or VAC or SAF (example…. Chris P – BOT)
Vacation Code next to name in Calendar in sheet 3 (example…. Chris P – BOT)
The only thing in the calendar will be the employee name in the first open cell in the calendar box for the date requested with the three digit (Code) after.
On calendar, there are 6 cells for each day, if first cell is occupied then put next name entered into next available cell under it.
I hope that kind of makes sense.
I have the Workbook designed with all the sheets ready to go, I just need help with the formulas.
Thanks so much.
Chris.
For-Submission.xlsx
ASKER
Thank you for you effort, I run PC Office 2013 at work, I am home now on my iMac. I have not tried your solution at work on PC yet, but on my iMAC Office 2011 I get error. It is probably my MAC excel that doesn't like the code.
Here is the error and reference to line it doesn't like.
Here is the error and reference to line it doesn't like.
me.BOT is trying to check whether the BOT radio button is on or off. You may try to find the appropriate syntax for MAC
ASKER
Hi Saqib
Same error on Windows Office 365
Also can not change the BOT VAC or SAF it is locked
Thanks.
Chris
Same error on Windows Office 365
Also can not change the BOT VAC or SAF it is locked
Thanks.
Chris
Does the month selection work?
ASKER
Hi there,
I can select different months by clicking and the "X" changes to the selected month, YES, this works.
But the scripts stops and error coms, and will not put anything into the calendar
Thanks
I can select different months by clicking and the "X" changes to the selected month, YES, this works.
But the scripts stops and error coms, and will not put anything into the calendar
Thanks
I am not sure why it is not working because it works just fine with me.
I am now working on replacing the optionbuttons with the "X"s
Stay put.
I am now working on replacing the optionbuttons with the "X"s
Stay put.
ASKER
Awesome, feel free to change the submit button, I hate my button, I have no design skill. LOL
Thanks
Thanks
Try this file
For-Submission--1-.xlsm
For-Submission--1-.xlsm
ASKER
Looks great,
Sorry for delay, I have to drive truck, I was away from office.
I have a couple more things I would like to add, Would you like me to start a new question for a couple more fixes to this?
Thanks/
Chris.
Sorry for delay, I have to drive truck, I was away from office.
I have a couple more things I would like to add, Would you like me to start a new question for a couple more fixes to this?
Thanks/
Chris.
Just carry on here.
ASKER
Ok great, you are awesome.
Just a couple more small fixes.
I have created 2 more SHEETS, One for BOT and one for SAF, so we have total of 3 destinations for the data:
1. User enters VAC hours- Data will go to "Chart" Sheet (Or we can rename VAC, what do you think?)
2. User enters BOT hours-Data will go to "BOT" Sheet
3. User enters SAF hours-Data will go to "SAF" Sheet
For all three types of input, all will still populate the calendar the same way as before for the day/date/month.
So the way it works now, with calendar being filled, will still be the same regardless the type VAC/BOT/SAF
The second little fix, is I would love to have a second copy of the workbook, (minus the dashboard) that will be read only, and viewable by anyone. I would like to be able to update/refresh data from master WorkBook when both WorkBooks are open with a click of a button or something. I hope this makes sense?
It's Friday afternoon here, I am off on the weekend and will check back first thing Monday morning again.
This is looking good.
Thank you very much.
Talk soon.
Chris
For-Submission--1-.xlsm
Just a couple more small fixes.
I have created 2 more SHEETS, One for BOT and one for SAF, so we have total of 3 destinations for the data:
1. User enters VAC hours- Data will go to "Chart" Sheet (Or we can rename VAC, what do you think?)
2. User enters BOT hours-Data will go to "BOT" Sheet
3. User enters SAF hours-Data will go to "SAF" Sheet
For all three types of input, all will still populate the calendar the same way as before for the day/date/month.
So the way it works now, with calendar being filled, will still be the same regardless the type VAC/BOT/SAF
The second little fix, is I would love to have a second copy of the workbook, (minus the dashboard) that will be read only, and viewable by anyone. I would like to be able to update/refresh data from master WorkBook when both WorkBooks are open with a click of a button or something. I hope this makes sense?
It's Friday afternoon here, I am off on the weekend and will check back first thing Monday morning again.
This is looking good.
Thank you very much.
Talk soon.
Chris
For-Submission--1-.xlsm
The BOT and Safety sheets should have the same layout as for Chart. I added one column each at column C
For-Submission--1-.xlsm
For-Submission--1-.xlsm
Is it ok for me to assume that the previous exercize was correct?
ASKER
Yes it works great thanks.
Now I just need to fix it better, to have each type of overtime go to each own chart.
And the person doing the calendar wants to be able to share a read only version of the calendar.
But is it possible to be able to refresh the read only version from the dashboard of the main program?
Thanks
You are a wizzard.
Chris
Now I just need to fix it better, to have each type of overtime go to each own chart.
And the person doing the calendar wants to be able to share a read only version of the calendar.
But is it possible to be able to refresh the read only version from the dashboard of the main program?
Thanks
You are a wizzard.
Chris
ASKER
Sorry I missed your fix.
Looks great. I think it is working.
Did you see the message about a read only copy that we can share with other people??
Thanks so much.
Chris
Looks great. I think it is working.
Did you see the message about a read only copy that we can share with other people??
Thanks so much.
Chris
How read-only. To the extent that someone does not mess up something inadvertantly; then yes. If you want to make it secure so that nobody can change it; then no.
ASKER
I want to make a copy of it that is read only or password protected, which ever is easiest.
I could unlock it, then sync, or update, or refresh it so it will be the same then close it again, then it will be protected when anyone ELSE try to change anything.
Does that make sense??
Thanks
Chris
I could unlock it, then sync, or update, or refresh it so it will be the same then close it again, then it will be protected when anyone ELSE try to change anything.
Does that make sense??
Thanks
Chris
I could unlock it.....then it will be protected when anyone ELSE try to change anything.I reemphasize my point here: If you want to protect it from anyone entering with the "intention" of hacking it then this is not possible in excel.
If you want to protect it against someone accidentally changing the data then this is possible.
So do you want to move in this direction?
ASKER
We are not worried about hackers, We have secured site. We just don't want people to accidentally make changes.
Hmmm I am thinking, Is it possible to have it read only unless you have a password then you can edit it?
This may be the easiest way to handle it.
Only one person will be entering the data, everyone else can see it, but we don't want accidental changes.
Thank you.
Chris
Hmmm I am thinking, Is it possible to have it read only unless you have a password then you can edit it?
This may be the easiest way to handle it.
Only one person will be entering the data, everyone else can see it, but we don't want accidental changes.
Thank you.
Chris
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great help. Appreciate the back and forth dialogue.
Very pleasant to work with.
Chris
Very pleasant to work with.
Chris
To select a month double click in the month box
To submit double-click the submit button
Make sure that macros are enabled.
For-Submission.xlsm