Log In
Register
Home
Active Member
November 26, 2024
Offline
Hi Team,
I have attached an excel file.
when I try to update leave, comp off, in my roaster sheet i want that to be updated in my other sheets dec / jan those are calender sheet.
How can I formulate this?
See Answer
July 16, 2010
Offline
Hi Sathish,
Your file isn't attached. To attach it, select the file, click 'Upload', then wait for the grey check mark beside the file size before pressing 'Submit Reply'.
Mynda
Active Member
November 26, 2024
Offline
attached
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Attached is an example which in my view is an easier and better approach, but you need of course an Excel version having the newer functions as TEXTJOIN and FILTER. I have simplified the table, but I hope I have added enough sample data so it's clear on how it is supposed to be used. Just add more data in the table and it will show in the calendar for the specified date.
Br,
Anders
Answers Post
Active Member
November 26, 2024
Offline
Great, this looks amazing.
Curious to know how did you make the calender with button? is there a vb script behind?
I have attached my current roster format, should i just mention the table name in formula and that will run through the whole table?
Please advice
VIP
Trusted Members
December 7, 2016
Offline
Hello,
The reason I choosed not to reuse your range of data is because it is in wrong format and not suitable as a table. Of course that can be fixed so it is in a tabular format. But then again, your range contains information I don't know if you want to show in the calendar view or not. It was not shown in your sample so I assumed not.
I also noticed that in the attached file in post 5 you have for some reason added data in the summary row, which won't work as the summary row is not part of the tables data section (body).
Regarding the spin buttons, you find them in the Developer tab, Controls section (Insert - Form Controls). No VBA there. Just right click a button and pick Format Control to see what the settings are for the button(s).
Br,
Anders
Active Member
November 26, 2024
Offline
Well, this is the format I use and normally the respective shifts are marked as S1, S2 and S3 ( S - shift)
Would mark the below
For leave - C-PL
WFH
Comp off - CO
So you mean to say that with my formatting you cant do it , correct?
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Based on that I always work with data in a tabular format and thus have not experimented with twisting the functions in a scenario like this, it's at least beyond my knowledge in how to formulate such formula.
But don't let that stop you from trying and experimenting. At least you have seen how you can go about using data in a tabular format. As always with Excel, there are more than one way to get to the results.
Br,
Anders
1 Guest(s)
