Extracting records that match different criteria for multiple reports in different sheets

[jayjavina]

Hi Excel Gurus,

I would like to ask for some assistance on how I can better manage this monthly report. Below are the conditions and the output will be shown in a different sheet in the below format

1. The DATA will be in a table format (Table1)
2. There will be a dropdown based on Location, Teacher & Name(Highlighted in Yellow)

• Post Will Be dependent on Location
• Name will also be dependent on the values selected on top

3. The Name selected will show the ACTUAL results from Table1 in the report table
4. The ACTUAL column will show each months values based on the individual selected.
5. The SAV column are savings based on the different tables at the bottom (Salary, Allowance & Tax Tables)
6. The report needs to be dynamic based on the values selected

I have tried using these formulas but my results didn't come out correct. Am not pretty sure on the accuracy of my data validation too.

=INDEX(A2:A17,AGGREGATE(15,6,(ROW(A2:A17)-ROW(A2)+1)/(A2:A17=$B$22),COLUMNS(B26:B26)),MATCH(A26,C1:K1,0))

=VLOOKUP(B26,A39:B43,2)

Please Help.

Thank you,
Jay



***This report will also be created in earlier versions of excel (2003-2010)

Table1

Name	Month	Location	Post	Salary	Allow1	Allow2	Allow3	Tax 1	Tax 2	Tax 3
Joe	Jan	East	Teacher	1771	30	46	72	37	65	64
Sioux	Jan	West	Admin	1147	92	28	49	61	93	56
Chin	Jan	West	Admin	1482	92	36	63	95	49	63
Max	Jan	East	Intern	1565	92	28	49	61	93	56
Tim	Jan	East	Teacher	1597	65	42	86	92	74	57
Joe	Feb	East	Teacher	1600	45	43	75	56	45	44
Sioux	Feb	West	Admin	1768	27	47	53	70	34	87
Chin	Feb	West	Admin	1189	38	43	73	44	48	96
Max	Feb	East	Intern	1482	82	42	87	68	29	62
Tim	Feb	East	Teacher	1597	65	42	86	92	74	57
Joe	Mar	East	Teacher	1597	65	42	86	92	74	57
Sioux	Mar	West	Admin	1921	82	42	87	68	29	62
Chin	Mar	West	Admin	1404	71	78	51	59	77	87
Max	Mar	East	Intern	1665	45	43	75	56	45	44
Tim	Mar	East	Teacher	1597	65	42	86	92	74	57
Joe	Apr	East	Teacher	1687	22	45	52	36	85	52

<tbody>
</tbody>

Location	East
Post	Teacher
Name	Joe
	Jan	Feb	Mar	Apr	May
	ACTUAL	SAV	ACTUAL	SAV	ACTUAL	SAV	ACTUAL	SAV	ACTUAL	SAV
Salary	1771	40
Allow1	30	5
Allow2	46	10
Allow3	72	15
Tax 1	37	2
Tax 2	65	6
Tax 3	64	6
Total		84
Salary Table			Allowance Table			Tax Table
1000	10		20	5		20	2
1200	20		40	10		40	4
1400	30		60	15		60	6
1600	40		80	20		80	8
1800	50		100	25		100	10

<tbody>
</tbody>

 

[Aladin Akyurek]

Table 1 is located on Sheet1, A:K, with headers in the first row.

Salary, Allowance, and Tax tables are Sheet3.

Sheet2, where processing takes place.

Location	East
Post	Teacher
Name	Joe
	Jan
	ACTUAL	SAV
Salary	1771
Allow1	30
Allow2	46
Allow3	72
Tax 1	37
Tax 2	65
Tax 3	64

<COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3299" width=93><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3868" width=109><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

B7, control+shift+enter, not just enter, and copy down:

=INDEX(Sheet1!$E$2:$K$17,MATCH(1,IF(Sheet1!$A$2:$A$17=$B$3,
  IF(Sheet1!$B$2:$B$17=B$5,IF(Sheet1!$C$2:$C$17=$B$1,
  IF(Sheet1!$D$2:$D$17=$B$2,1)))),0),MATCH($A7,Sheet1!$E$1:$K$1,0))

I have no idea what SAV is, so tere is no formula proposed for it.

 

[jayjavina]

Thanks Aladin! Appreciate the help

Let me try to work it out at my end.

SAV means savings. its actually the Salary, allowance and tax tables.
Savings is based on the ACTUAL value. thats why I used a lookup function to get the desired savings based on the values on the left table or the ACTUAL column.
For example with Joe, since he has a salary of 1771, he will then get a savings of 40. am not sure if am doing it correctly.

My question on the data validation. on the yellow highlighted cells, how can i make it work that if i select east in the Location section, only those posts on the east will appear then the names available will be selected based on the post. what i mean is on those related values will come out if they are selected.

thanks,
Jay

 

[Aladin Akyurek]

Thanks Aladin! Appreciate the help

Let me try to work it out at my end.

Ok. Here is the workbook I used:

https://dl.dropboxusercontent.com/u/65698317/jayjavina%20conditional%20index%20match.xlsx

SAV means savings. its actually the Salary, allowance and tax tables.
Savings is based on the ACTUAL value. thats why I used a lookup function to get the desired savings based on the values on the left table or the ACTUAL column.
For example with Joe, since he has a salary of 1771, he will then get a savings of 40. am not sure if am doing it correctly.

If you know how to calculate the savings manually, we can try to map that onto Excel

My question on the data validation. on the yellow highlighted cells, how can i make it work that if i select east in the Location section, only those posts on the east will appear then the names available will be selected based on the post. what i mean is on those related values will come out if they are selected.
[...]

If you want to create the required lists for data validation directly from the data (i.e., if you don't have preset lists elsewhere), you would probably need VBA. You can start another thread for that purpose.