Use of index for multiple sheets and match for three criteria

[sucitanr]

---

Hi ,

I am making a format of salary slip using index and match function.
i am trying that if i put the month and emp. id the rest data should come automatically to that payslip format. but i have 12 worksheet having 12 month salary on it. and in that i have already made table of the salary made for particular month naming the table name.
now i want to use index formula for all the worksheet and match formula in such a way that if i just put the month and emp id i get the pay slip made for the particluar employee.
my every worksheet has the below header:-

[TABLE="class: gmail-wysiwyg_dashes, width: 300"]
<tbody>[TR]
[TD]emp id[/TD]
[TD]name[/TD]
[TD]month[/TD]
[TD]lwp[/TD]
[TD]salary payable[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]praveen[/TD]
[TD]may'18[/TD]
[TD]1[/TD]
[TD]18000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]deepak[/TD]
[TD]may'18[/TD]
[TD]0[/TD]
[TD]9000[/TD]
[/TR]
</tbody>[/TABLE]


this is may month salary worksheet. like this i have 11 more month salary work sheet.

and i have made a format of payslip in which i have put all the details.i am trying for a formula through which if i just put month and emp id ,i get the data in the desire colums of that payslip from the desired worksheet.

Plz can u help.

 

[Kamolga]

If I understand you well, you have 12 times (for 12 months) the same kind of sheet structure. You know sufficiently index match formula to get it on one sheet (let's say may'18 reference is in B2 and your sheet is called may'18). The easiest way is then to take you formula and change

[LEFT][COLOR=#008000][FONT=Verdana]may'18![/FONT][/COLOR][/LEFT]

(which is the reference to the page) by

[LEFT][COLOR=#222222][FONT=Verdana]indirect(B2&"!")[/FONT][/COLOR][/LEFT]

; so that as long as B2 refers to sheet name, it will use that sheet in the formule. If you named tables, you can use indirect formula as well.

Another option is to use power pivot (a pivot table on 12 sheets), with the inconvenient of refresh.

 

[sucitanr]

[TABLE="width: 300"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]month[/TD]
[TD]to put manually[/TD]
[/TR]
[TR]
[TD]employee id[/TD]
[TD]to put manually[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD]amount through formula[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]lwp[/TD]
[TD]amount through formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]salary payable[/TD]
[TD]amount through formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

i want to keep formula of index and match in all name , lwp and salary payable column so that if i just put month and employee id , the three column should catch their amount/details from the respective sheet. Like if i put "may" in month and employee id as "1" it should pick data of the employee id - " 1" from "May'18" month worksheet. and if i put "jun" in month column and employee id as "1" it should pick data of the employee id - " 1" from "jun'18" month worksheet

Plz help me with full formula, as i am unable to keep two matching cells with multiple sheets.

thanks for quick response.

 

[Aladin Akyurek]

How are your sheets named?

 

[sucitanr]

i have already selected and made the table on seat naming as may_18, jun_18 to apr_19 for using Index function.
Whereas my sheets named as may'18 , jun'18 to apr'19.

thanks for helping.

 

[Aladin Akyurek]

Care to post 5 rows from may'18?

 

[sucitanr]

i can send you the screen shot or can send you the sheet but i dont know if i can send it from here. i am unable to find any attachment kind of button here,

 

[Aladin Akyurek]

i can send you the screen shot or can send you the sheet but i dont know if i can send it from here. i am unable to find any attachment kind of button here,

Please try to post here directly: just 5 rows of may'18?

 

[sucitanr]

[TABLE="width: 300"]
<tbody>[TR]
[TD]Employee id[/TD]
[TD]name[/TD]
[TD]month[/TD]
[TD]LWP[/TD]
[TD]Salary Payable[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Prasun[/TD]
[TD]May'18[/TD]
[TD]1[/TD]
[TD]12000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Alok[/TD]
[TD]May'18[/TD]
[TD]0[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Rahul[/TD]
[TD]May'18[/TD]
[TD]0[/TD]
[TD]9000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Deepak[/TD]
[TD]May'18[/TD]
[TD]0[/TD]
[TD]8000[/TD]
[/TR]
</tbody>[/TABLE]


I have named the table as May_18 and the sheet name is May'18

[TABLE="width: 300"]
<tbody>[TR]
[TD]Employee id[/TD]
[TD]name[/TD]
[TD]month[/TD]
[TD]LWP[/TD]
[TD]Salary Payable[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Prasun[/TD]
[TD]Jun'18[/TD]
[TD]1[/TD]
[TD]12000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Alok[/TD]
[TD]jun'18[/TD]
[TD]0[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Rahul[/TD]
[TD]jun'18[/TD]
[TD]0[/TD]
[TD]9000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Deepak[/TD]
[TD]jun'18[/TD]
[TD]0[/TD]
[TD]8000

[/TD]
[/TR]
</tbody>[/TABLE]


I have named the table as jun_18 and the sheet name is jun'18

Like this i have made the monthly salary sheet from may'18 to apr'19.


PAYSLIP I HAVE MADE IN A DIFFERENT WORKSHEET SOMEWHAT LIKE THIS:-

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]COMPANY NAME[/TD]
[TD][/TD]
[TD]MONTH[/TD]
[TD]MAY'18[/TD]
[/TR]
[TR]
[TD]EMP ID[/TD]
[TD]1[/TD]
[TD]FILL THIS COLUMN OF EMP ID AND MONTH MANUALLY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME[/TD]
[TD][/TD]
[TD]WILL PUT FORMULA IN NAME COLUMN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LWP[/TD]
[TD][/TD]
[TD]WILL PUT FORMULA IN LWP COLUMN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Salary Payable[/TD]
[TD][/TD]
[TD]WILL PUT FORMULA IN SALARY PAYABLE COLUMN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD]TOTAL AMOUNT PAYABLE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I WANT THE FORMULA IN SUCH A WAY THAT I ONLY FILL MONTH AND EMP ID AND I AUTOMATICALLY GET THE EMPL CODE DETAILS FROM THE RESPECTIVE MONTH SHEET.

I TRIED ONE FORMULA USING INDEX AND MATCH BUT IT WAS TAKING ONLY ONE SHEET AND WITH ONLY ONE MATCH. PLZ CAN YOU TELL ME HOW TO MAKE THE FORMULA.

MY FORMULA WAS :-
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #0057d6}span.s2 {color: #006107}span.s3 {color: #ab30d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}</style>
=INDEX('May ''18'!$B$2:$E$5,MATCH($A$2,'May ''18'!$A$2:$A$5,0),MATCH(D3,'May ''18'!$B$2:$E$5,0))

I HAVE PUT THE ABOBE FORMULA IN NAME AND HAVE COPIED THE SAME IN LWP AND SALARY PAYABLE COLUMN.

BUT THIS IS ONLY GIVING ME THE MAY MONTH DATA TAKING EMPID AS A MATCH COLUMN WHICH DOES NOT SOLVE MY PURPOSE.

I WANT TO TAKE ALL SHEETS AND WANT TO USE EMP ID AND MATCH COULMN AS MATCH .


THANKS FOR TH QUICK RESPONSE.

 

[sucitanr]

[TABLE="class: cms_table, width: 300"]
<tbody>[TR]
[TD]Employee id[/TD]
[TD]name[/TD]
[TD]month[/TD]
[TD]LWP[/TD]
[TD]Salary Payable[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Prasun[/TD]
[TD]May'18[/TD]
[TD]1[/TD]
[TD]12000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Alok[/TD]
[TD]May'18[/TD]
[TD]0[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Rahul[/TD]
[TD]May'18[/TD]
[TD]0[/TD]
[TD]9000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Deepak[/TD]
[TD]May'18[/TD]
[TD]0[/TD]
[TD]8000[/TD]
[/TR]
</tbody>[/TABLE]



I have named the table as May_18 and the sheet name is May'18

[TABLE="class: cms_table, width: 300"]
<tbody>[TR]
[TD]Employee id[/TD]
[TD]name[/TD]
[TD]month[/TD]
[TD]LWP[/TD]
[TD]Salary Payable[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Prasun[/TD]
[TD]Jun'18[/TD]
[TD]1[/TD]
[TD]12000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Alok[/TD]
[TD]jun'18[/TD]
[TD]0[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Rahul[/TD]
[TD]jun'18[/TD]
[TD]0[/TD]
[TD]9000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Deepak[/TD]
[TD]jun'18[/TD]
[TD]0[/TD]
[TD]8000
[/TD]
[/TR]
</tbody>[/TABLE]



I have named the table as jun_18 and the sheet name is jun'18

Like this i have made the monthly salary sheet from may'18 to apr'19.


PAYSLIP I HAVE MADE IN A DIFFERENT WORKSHEET SOMEWHAT LIKE THIS:-

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"]COMPANY NAME[/TD]
[TD][/TD]
[TD]MONTH[/TD]
[TD]MAY'18[/TD]
[/TR]
[TR]
[TD]EMP ID[/TD]
[TD]1[/TD]
[TD]FILL THIS COLUMN OF EMP ID AND MONTH MANUALLY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME[/TD]
[TD][/TD]
[TD]WILL PUT FORMULA IN NAME COLUMN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LWP[/TD]
[TD][/TD]
[TD]WILL PUT FORMULA IN LWP COLUMN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Salary Payable[/TD]
[TD][/TD]
[TD]WILL PUT FORMULA IN SALARY PAYABLE COLUMN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD]TOTAL AMOUNT PAYABLE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I WANT THE FORMULA IN SUCH A WAY THAT I ONLY FILL MONTH AND EMP ID AND I AUTOMATICALLY GET THE EMPL CODE DETAILS FROM THE RESPECTIVE MONTH SHEET.

I TRIED ONE FORMULA USING INDEX AND MATCH BUT IT WAS TAKING ONLY ONE SHEET AND WITH ONLY ONE MATCH. PLZ CAN YOU TELL ME HOW TO MAKE THE FORMULA.

MY FORMULA WAS :-

=INDEX('May ''18'!$B$2:$E$5,MATCH($A$2,'May ''18'!$A$2:$A$5,0),MATCH(D3,'May ''18'!$B$2:$E$5,0))

I HAVE PUT THE ABOvE FORMULA IN NAME AND HAVE COPIED THE SAME IN LWP AND SALARY PAYABLE COLUMN.

BUT THIS IS ONLY GIVING ME THE MAY MONTH DATA TAKING EMPID AS A MATCH COLUMN WHICH DOES NOT SOLVE MY PURPOSE.

I WANT TO TAKE ALL SHEETS AND WANT TO USE EMP ID AND Month COULMN AS MATCH .

Plz can u hep me wit full formula as i am not good with excel . I tried to make above formula but it was not fully helpful..

Thanks