Leave balance Carry Forward

U

uswyne

Board Regular
Joined
Jul 27, 2017
Messages
78
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2010
Platform
  1. Windows
[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Emp #[/TD]
[TD]Opening Casual Leave[/TD]
[TD]Availed Casual Leave[/TD]
[TD]Casual Leave Balance [/TD]
[/TR]
[TR]
[TD]Sep 18[/TD]
[TD]1[/TD]
[TD]05[/TD]
[TD]01[/TD]
[TD]04 =(C1-D1)[/TD]
[/TR]
[TR]
[TD]Sep 18[/TD]
[TD]2[/TD]
[TD]07[/TD]
[TD]02[/TD]
[TD]05 =(C2-D2)[/TD]
[/TR]
[TR]
[TD]Oct 18[/TD]
[TD]1[/TD]
[TD]04 ? auto fill[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct 18[/TD]
[TD]2[/TD]
[TD]05 ? auto fill[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

how to opening casual leave auto fill while entering new month payroll?
 
Sort by date Sort by votes
Hello,

Just made a quick test ... which you can see below ... :wink:


Sheet1

ABCDE
Emp #Opening Casual Leave

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:100px;"><col style="width:49px;"><col style="width:150px;"><col style="width:148px;"><col style="width:153px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]Month[/TD]

[TD="align: center"]Availed Casual Leave[/TD]
[TD="align: center"]Casual Leave Balance[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]Sep-18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]Sep-18[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]Oct-18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]Oct-18[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]Nov-18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]Nov-18[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]Dec-18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: center"]Dec-18[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
E2=C2-D2
E3=C3-D3
C4{=INDEX($E$2:$E$10,MATCH(EDATE(A4,-1)&B4,$A$2:$A$10&$B$2:$B$10,0))}
E4=C4-D4
C5{=INDEX($E$2:$E$10,MATCH(EDATE(A5,-1)&B5,$A$2:$A$10&$B$2:$B$10,0))}
E5=C5-D5
C6{=INDEX($E$2:$E$10,MATCH(EDATE(A6,-1)&B6,$A$2:$A$10&$B$2:$B$10,0))}
E6=C6-D6
C7{=INDEX($E$2:$E$10,MATCH(EDATE(A7,-1)&B7,$A$2:$A$10&$B$2:$B$10,0))}
E7=C7-D7
C8{=INDEX($E$2:$E$10,MATCH(EDATE(A8,-1)&B8,$A$2:$A$10&$B$2:$B$10,0))}
E8=C8-D8
C9{=INDEX($E$2:$E$10,MATCH(EDATE(A9,-1)&B9,$A$2:$A$10&$B$2:$B$10,0))}
E9=C9-D9

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
i am using the same formula, while checking Trace Precedents in C6, trace shows month of September (A2) rather than month of October (A4).

James006 said:
Hello,

Just made a quick test ... which you can see below ... :wink:


Sheet1

ABCDE
Emp #Opening Casual Leave

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Month[/TD]

[TD="align: center"]Availed Casual Leave[/TD]
[TD="align: center"]Casual Leave Balance[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Sep-18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Sep-18[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Oct-18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Oct-18[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Nov-18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Nov-18[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]Dec-18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]Dec-18[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
E2=C2-D2
E3=C3-D3
C4{=INDEX($E$2:$E$10,MATCH(EDATE(A4,-1)&B4,$A$2:$A$10&$B$2:$B$10,0))}
E4=C4-D4
C5{=INDEX($E$2:$E$10,MATCH(EDATE(A5,-1)&B5,$A$2:$A$10&$B$2:$B$10,0))}
E5=C5-D5
C6{=INDEX($E$2:$E$10,MATCH(EDATE(A6,-1)&B6,$A$2:$A$10&$B$2:$B$10,0))}
E6=C6-D6
C7{=INDEX($E$2:$E$10,MATCH(EDATE(A7,-1)&B7,$A$2:$A$10&$B$2:$B$10,0))}
E7=C7-D7
C8{=INDEX($E$2:$E$10,MATCH(EDATE(A8,-1)&B8,$A$2:$A$10&$B$2:$B$10,0))}
E8=C8-D8
C9{=INDEX($E$2:$E$10,MATCH(EDATE(A9,-1)&B9,$A$2:$A$10&$B$2:$B$10,0))}
E9=C9-D9

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
Hi,

Sadly ... cannot see what is actually in front of your eyes ...

Can only confirm to you the formula functions as expected ...

Have you tried to replicate exactly the example posted in message #11 ...???
 
Upvote 0
Pleased to hear you have managed to fix your problem ... :smile:
 
Upvote 0
Thanks, can u recommend me any book or resource to learn excel in depth, though this forum is too good.

and sorry to bother u again n again;)

James006 said:
Pleased to hear you have managed to fix your problem ... :smile:
Click to expand...
 
Upvote 0
You are welcome ...

In my opinion, the best is to learn with the BEST :

John Walkenbach

In addition to his great books ... his site is truly fantastic ...!!!

http://spreadsheetpage.com/

Hope you will enjoy ...:smile:
 
Upvote 0
HI there, can u help me out [TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Table 1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Uniform[/TD]
[TD="align: center"]Issue Date[/TD]
[TD="align: center"]Due Date[/TD]
[TD="align: center"]Shoe[/TD]
[TD="align: center"]Issue Date[/TD]
[TD="align: center"]Due Date[/TD]
[/TR]
[TR]
[TD="align: center"]AAA[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]31-Dec-18[/TD]
[TD="align: center"]31-Dec-18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]01-Jan-18[/TD]
[TD="align: center"]01-Jan-19[/TD]
[/TR]
[TR]
[TD="align: center"]BBB[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]30-Nov-17[/TD]
[TD="align: center"]30-Nov-18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]31-Dec-17[/TD]
[TD="align: center"]31-Dec-18[/TD]
[/TR]
[TR]
[TD="align: center"]CCC[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]25-Nov-17[/TD]
[TD="align: center"]25-Nov-18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]30-Novv-17[/TD]
[TD="align: center"]30-Nov-18[/TD]
[/TR]
</tbody>[/TABLE]


I need a formula to extract data from table 1 in to table given below.

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Nov-18[/TD]
[TD]Dec-18[/TD]
[TD]Jan-19[/TD]
[/TR]
[TR]
[TD]Uniform[/TD]
[TD]02 (?)[/TD]
[TD]01 (?)[/TD]
[TD]00 (?)[/TD]
[/TR]
[TR]
[TD]Shoes[/TD]
[TD]01 (?)[/TD]
[TD]01 (?)[/TD]
[TD]01 (?)[/TD]
[/TR]
</tbody>[/TABLE]



James006 said:
You are welcome ...

In my opinion, the best is to learn with the BEST :

John Walkenbach

In addition to his great books ... his site is truly fantastic ...!!!

http://spreadsheetpage.com/

Hope you will enjoy ...:smile:
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
126
flashgordie
F
M
  • Solved
How to count certain month from a column
Replies
3
Views
395
Joe4
Joe4
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P
M
  • Question Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
664
Peter_SSs
Peter_SSs
A
  • Solved
Macro getting stuck on average when no data is present
Replies
5
Views
429
amphead
A

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top