Leave balance Carry Forward

[uswyne]

Month	Emp #	Opening Casual Leave	Availed Casual Leave	Casual Leave Balance
Sep 18	1	05	01	04 =(C1-D1)
Sep 18	2	07	02	05 =(C2-D2)
Oct 18	1	04 ? auto fill
Oct 18	2	05 ? auto fill

<tbody>
</tbody>

how to opening casual leave auto fill while entering new month payroll?

 

[James006]

Hello,

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


Sheet1

	A	B	C	D	E
1	Month	Emp #	Opening Casual Leave	Availed Casual Leave	Casual Leave Balance
2	Sep-18	1	5	1	4
3	Sep-18	2	7	2	5
4	Oct-18	1	4	1	3
5	Oct-18	2	5	1	4
6	Nov-18	1	3	1	2
7	Nov-18	2	4	1	3
8	Dec-18	1	2	1	1
9	Dec-18	2	3	1	2

<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>
</tbody>

Spreadsheet Formulas

Cell Formula 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

 

[uswyne]

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

	A	B	C	D	E
1	Month	Emp #	Opening Casual Leave	Availed Casual Leave	Casual Leave Balance
2	Sep-18	1	5	1	4
3	Sep-18	2	7	2	5
4	Oct-18	1	4	1	3
5	Oct-18	2	5	1	4
6	Nov-18	1	3	1	2
7	Nov-18	2	4	1	3
8	Dec-18	1	2	1	1
9	Dec-18	2	3	1	2

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula 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

 

[James006]

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

 

[uswyne]

Its done I have issue with my table. I resolve the issue

Thank u so much to help me

Kindly add Nov and Dec in Coloumn A and Emp # in Column B

 

[James006]

Pleased to hear you have managed to fix your problem ...

 

[uswyne]

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

 

[James006]

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

 

[uswyne]

HI there, can u help me out

Table 1
Name	Uniform	Issue Date	Due Date	Shoe	Issue Date	Due Date
AAA	2	31-Dec-18	31-Dec-18	1	01-Jan-18	01-Jan-19
BBB	2	30-Nov-17	30-Nov-18	1	31-Dec-17	31-Dec-18
CCC	2	25-Nov-17	25-Nov-18	1	30-Novv-17	30-Nov-18

<tbody>
</tbody>

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

A	B	C	D
	Nov-18	Dec-18	Jan-19
Uniform	02 (?)	01 (?)	00 (?)
Shoes	01 (?)	01 (?)	01 (?)

<tbody>
</tbody>

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