Auto update from one sheet to another

Question

In my worksheet, I am organizing coworkers vacation times.

Instead of Sheet1, Sheet2, Sheet3, etc, I have each sheet named after all the coworkers.

Within every coworkers sheet, I put all the dates that they booked off for the year. So for example, I booked off June 25th and June 26th.

The last sheet is an Excel Calendar that I downloaded. It's great because this Calendar allows me change the year, and it automatically updates it.

Anyways, I would like to be able to put all the dates that I have off, in my sheet (Sheet2...aka Chris) and as soon as I do that, It automatically inputs my name on the dates in the actual Calendar.

So, let's say I use cells A1 to A20 in each person's sheet to put in the dates they booked off. What formula or function can make my their name fet inputted into the calendar, on the exact date that was entered in sheet?

Sheet name: Chris
A1: June 25/2022
A2: June 26/2022
A3: June 30/2022

The Calendar sheets are named 1 through 12. Obviously June is in the sheet named '6'.

Heck, I suppose if someone can figure out how to do this, where do I even put the formula/function?

Sorry for such a long read.

Thanks for all advice.
Chris

riny_van_eekelen · Answer

Fukesy73&nbsp;First of all, which calendar template did you download? There are many different ones to be found on-line and most are notoriously difficult to adopt to ones own specific needs.&nbsp;Secondly, it's probably not the most effective approach to have all co-workers in separate sheets with separate calendars for each of them. Wouldn't you want one list of all days off and show them all in one calendar?

fukesy73 · Answer

0

I understand what you're saying. My spreadsheet only has the one calendar. For whatever reason the file is missing the calendar on my phone, but when I open it on the laptop, the calendar is there. So I can't screenshot it.

Anyways, the only reason I have a sheet dedicated for each of us, is so I can see what a person has left for vacation time.

Then the one calendar is at the end of the worksheet. So I was hoping to have each of our individual sheets input the workers names into the same calendar at the end.

I'll find out what calendar template I have, once I open my calendar.

I suppose I should just have one sheet, input all the dates that are booked off in, say, column A, and then put the person's name who booked it off, in column B. That way I just have to take info from one sheet, instead of several.

mathetes · Answer

Fukesy73&nbsp;&nbsp;You wrote:... the only reason I have a sheet dedicated for each of us, is so I can see what a person has left for vacation time.&nbsp;....I suppose I should just have one sheet, input all the dates that are booked off in, say, column A, and then put the person's name who booked it off, in column B. That way I just have to take info from one sheet, instead of several.&nbsp;It made sense, back in the days of record keeping on paper, to have separate sheets for each person. Sadly, we often carry forward into the computer age the same mindset. It helps to recognize that Excel works well, and is easier to work with, if you put data like this into a single database (or table). I strongly suspect that's what Riny_van_Eekelen&nbsp;was getting at with his comments.&nbsp;Once you have a single database for all employees, you can (as you say in the second paragraph above)-- just identify each person with their names in a column headed "Name" or "Employee". And then have a separate sheet that extracts the desired info -- e.g., "how much time does Alan have left for vacation?" or if desired, other kinds of summary data, e.g., by department.

riny_van_eekelen · Answer

Fukesy73&nbsp;This particular Calendar is not going to help you. It is just creating monthly calendar view that you can print out on paper and write notes on. The is no intelligence in this template that can dynamically list events. You would be better of by creating a list of your co-workers beginning balances and days planned/taken. You can the set-up some pivot tables to create the reports you need/want.&nbsp;A very crude example of such a "system" is included in the attached file.&nbsp;

fukesy73 · Answer

Thanks so much. That table seems like a more simplified version, of the mess I was doing.I was over-thinking it too much.I went overboard because our automated payroll system wasn't working.So a pivot table will allow me to put every employees name in, how much vacation time(hrs) each person has in total....and when I put the date in the table, it should calculate what they used and how much time they have left.

Forum Discussion

Auto update from one sheet to another

5 Replies

Resources