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 pikeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Hi, I could not resist making changes to your worksheet.

To select a month double click in the month box
To submit double-click the submit button

Make sure that macros are enabled.
chris pikeAuthor Commented:
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.
Saqib Husain, SyedEngineerCommented:
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
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

chris pikeAuthor Commented:
Hi Saqib
Same error on Windows Office 365

BOT error
Also can not change the BOT VAC or SAF it is locked
Saqib Husain, SyedEngineerCommented:
Does the month selection work?
chris pikeAuthor Commented:
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
Saqib Husain, SyedEngineerCommented:
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.
chris pikeAuthor Commented:
Awesome, feel free to change the submit button, I hate my button, I have no design skill. LOL
Saqib Husain, SyedEngineerCommented:
Try this file
chris pikeAuthor Commented:
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?
Saqib Husain, SyedEngineerCommented:
Just carry on here.
chris pikeAuthor Commented:
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.

Saqib Husain, SyedEngineerCommented:
The BOT and Safety sheets should have the same layout as for Chart. I added one column each at column C
Saqib Husain, SyedEngineerCommented:
Is it ok for me to assume that the previous exercize was correct?
chris pikeAuthor Commented:
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?


You are a wizzard.
chris pikeAuthor Commented:
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.
Saqib Husain, SyedEngineerCommented:
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.
chris pikeAuthor Commented:
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??
Saqib Husain, SyedEngineerCommented:
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?
chris pikeAuthor Commented:
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.
Saqib Husain, SyedEngineerCommented:
I am sorry I have not been able to spend time on your request. You can simply put a password on the sheets you want to protect.

It that is not good enough then please ask a new question. You stand a better chance to get a response soon.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chris pikeAuthor Commented:
Great help. Appreciate the back and forth dialogue.
Very pleasant to work with.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.