Leave Balance report

[ambz123]

Hi,

I am task to create a leave balance report. I would like to ask how do i linkup using data validation to show the used leave and balance leave in the list below. Is there any way whereby i can select from the dropped down list, for example when i selected "USED" the data below will show only the used leave. And when i selected "BALANCED" it will only show balance leave.

Appreciate help on that.

Thank you.

 

[AlanY]

see if you can work something out from this example

Book1
	A	B	C	D	E	F	G
1		Entited	Used	Balance		Display	Balance
2	Name1	7	1	6		Name1	6
3	Name2	7	2	5		Name2	5
4	Name3	14	3	11		Name3	11
5	Name4	14	4	10		Name4	10
6	Name5	14	5	9		Name5	9
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=B2-C2
G2:G6	G2	=INDEX($B$2:$D$6,MATCH(F2,$A$2:$A$6,0),MATCH($G$1,$B$1:$D$1,0))

Cells with Data Validation
Cell	Allow	Criteria
G1	List	=$B$1:$D$1

 

[ambz123]

Hi Alan, thanks for the suggestion. As i have more than 1 type of leave, is there any other alternative ways for me to show the "Used" or "balanced" leave?

 

[AlanY]

you can extend the table like this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Entited1	Entited2	Entited3	Used1	Used2	Used3	Balance1	Balance2	Balance3		Display	Balance2
2	Name1	7	6	5	1	2	3	6	4	2		Name1	4
3	Name2	7	6	5	2	3	4	5	3	1		Name2	3
4	Name3	14	13	12	3	4	5	11	9	7		Name3	9
5	Name4	14	13	12	4	5	6	10	8	6		Name4	8
6	Name5	14	13	12	5	6	7	9	7	5		Name5	7
Sheet1

Cell Formulas
Range	Formula
M2:M6	M2	=INDEX($B$2:$J$6,MATCH(L2,$A$2:$A$6,0),MATCH($M$1,$B$1:$J$1,0))

Cells with Data Validation
Cell	Allow	Criteria
M1	List	=$B$1:$J$1

 

[ambz123]

Okay. Which means my drop down list will have

Entitled 1
Entitled 2
Entitled 3
and so on?

 

[ambz123]

.