Leave balance Carry Forward

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?
 
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
i am using the same formula, while checking Trace Precedents in C6, trace shows month of September (A2) rather than month of October (A4).

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
 
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;)

Pleased to hear you have managed to fix your problem ... :smile:
 
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]



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

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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