Forum Discussion
Excel Annual Leave calculation
Had you intended to attach a copy (or a mockup with names changed) of the spreadsheet? If so, it's not here. If not, please do anyway. Use OneDrive or GoogleDrive and post a link here that grants access.
Then, if I might be so bold, don't try to write the formula for us. Instead, describe in words what you want done. You can refer to the column heads (if there's also an attached spreadsheet), but don't try to write the formula for us--that's what you're coming to ask for help on. Describing it in words is the best way to start off.
Good Morning,
Apologies for the hubris! I shall use Google Drive, but you shall have to bear with me as I need to recreate the spreadsheet with no names 🙂
EDIT: link removed as newer one available
I hope you're planning on returning to flesh this out some more. You'll also need to give some assistance in navigating this maze, connecting it to your original questions.
Good Morning mathetes
I'm only able to get back to you whilst at work hence the sporadic replies, but yes I do intend to!
So each Ward has a relevant capture column for staffing levels which is found in the 'Daily Totals' sheet.
Each Ward also has it's own "dashboard", which is the purple boxes across the top, where all primary data can be entered to populate the rota below.
Some of the staff have established A/L Entitlements (see the relevant section in all Ward sheets) and for the most part, this is decided by their role; ie: all 'F1' have 9 days A/L per rota period.
As somewhat explained in my original message the way that I am hoping the formula will work is by:
[per Ward] (cells for the formula to be in 'Daily Totals' sheet around row 132
- Summing all the A/L Taken
- Subtracting any rows value, if the corresponding cell determining the staff members role meets a pre-defined criteria (TBD by range within the 'Data Admin' sheet.
- And/Or subtracting any rows value if there is no entry for A/L Entitlement
The aim is to achieve a 'True' value of how much leave has been taken by Ward, that should be monitored, as some staff are responsible for tracking their own A/L which is why I would like the formula to only capture certain roles in it's output.
As I write this, I can see this needing two separate formulas, but I can work with whatever solutions you suggest!EDIT: If you agreeable, please email me for quicker and potentially(?) easier communication (I will put it in 'Daily Totals'!A1)
Kind Regards,
OliYou really need to realize that just understanding what each of the individual sheets is about is a task in itself.
It's clear to you, but really isn't to somebody from the outside, and it's frankly not realistic to expect us to take the time to figure it out. If we were sitting down face-to-face we could probably come, pretty quickly, to an understanding of what all those entries represent, what each row and column are for, etc., etc. But we're not.
It would make more sense for you to provide a clear explanation here, not in a private communication, so that any of the other regular Excel-answer people looking at this can follow it, in part because there are others far more expert in some aspects of Excel than I, in part because I don't have all the time in the world, especially at this season of the year.
So I'd suggest you start with a clear explanation of what is being captured--and HOW it's being captured--on each sheet. What all your abbreviations mean, etc., and what is you're trying to accomplish. Don't assume it's clear. Assume, though, that you're speaking (writing) to an intelligent person but one who's never seen this layout before.