Excel Spreadsheet Help

[JBW1366]

I’m trying to do a spreadsheet for work and it’s for staff holidays so I have four columns with column C the amount in hours they’ve asked for and column D their remaining leave - is there a sim for it to work it out automatically? All the way down the more they ask for? I need to use this some for petty cash sheet as well. I’ve been looking for ages. Thanks in advance

[THECORBYLOON]

Are you thinking on a single sheet to list everyone's leave requests and remaining entitlement?

I would make it simple and use a tab for each member of staff.

Having said that, I was a civil servant for decades til a few year ago and we used spreadsheets for all sorts but staff noted their leave periods on individual clerical A5 sheets which their managers initialed to agree them.

Why do you want to include petty cash details on an annual leave spreadsheet?

[THECORBYLOON]

"Initialled" even...

[Zacs-Master]

https://trumpexcel.com/excel-leave-tracker/

[JBW1366]

I have created a separate tab for each staff member so wanted a sum I could carry over tbh. Also I have created another sheet for petty cash again with separate tabs - thanks

[bobbinwales]

Isn't the sum just c1 - do or do you want a formular to work out entitlement... that's just based on your companies rules unless it's just the statutary minimum

[bobbinwales]

C1- D1

[bobbinwales]

What's your column A and b got in them?

[JBW1366]

Column A has name column B department. So will c1-d1 carry on when I add extra rows when they ask for more leave? I’m currently using paper sheets for each person might carry on this is proving complicated

[bobbinwales]

OK so you need to put dates and number of days took each time and total them.... then deduct from entitlement which is a fixed cell

[JBW1366]

Yes I have entitlement in D1 then I put in dates in column b don’t need department then number of total hours used in C column each time they ask for leave so which I will need deducted from entitlement as it reduces - makes sense?

[bednobs]

I don't get why you'd need to add in lines later. You know how many days each person can have as a maximum, so put that number if lines in

[THECORBYLOON]

A line is added as and when each time a new period of leave is requested.

[Gettingonabit]

You need a running total in column C. Eg C15. Allows for multiple applications. Then in column E use formula D1-C15.

[JBW1366]

Hi thanks why C15 - so a new column just to work out column d?

[Gettingonabit]

I thought you said C was the entitlement used for that particular leave application. So each application needs to be added together and taken from the total entitlement which I understood to be D1.

[THECORBYLOON]

This is a very basic suggestion

A10 "Entitlement "
B10 put in number of hours
A11 "Balance"
B11 =B10-SUM(C:C)
A15 "From"
B15 "To"
C15 "Hours"

Then enter each leave period on a new line.

If you want to use the same tab for the next leave year, put the from and to dates as normal but show that entitlement as a NEGATIVE number.

[Tuvok]

Working on a 1 person per sheet system

Column headings are
A Name B Department C Entitlement D From E To F No. Hours G Balance

First Row (2nd row in spreadsheet)
A Mickey Mouse B Disney C 100 D 21 July E 23 July F 2 G =sum(c2-f2)

Second and subsequent rows
G =sum(g2-f3)

You then copy that formula into each row

[Tuvok]

How many employees do you have - as you may have emought that you can do then on one sheet.

As for petty cash assuming you have a fixed amount per month

Date | Who | Why | How Much | Balance
1 .july | opening balance | blank (Why column) | £100
4 July | Mickey Mouse | Printer ink | £12.33 | =sum(e2-e3)

[Maydup]

If you hover over the cell with the formula in, you will see a small square in the corner. Click on the small square to drag the formula down the column for all the subsequent rows of leave requests.

[Maydup]

One you have the first sheet working well, copy it as a new tab and simply change the name of the employee and rename that tab by right clicking on the tab name