File name in formula should change automatically

[rival36]

Kindly help with this sheet. thanks a lot.

I have raw data in OTHER excel file named test sheet 2. The yellow table of this test sheet 1 displaying data from that sheet (test sheet 2) as per formula. I am having data in multiple sheets named test sheet 3,4,5,6.... and so on. I want that when I drag this formula down, the file name in formula should change automatically to test sheet 3,4,5.... and so on to display data from different sheets at same time. I can write file names in this test sheet 1 if needed for reference.

mini sheet attached for reference

thanks

test sheet 1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2
3
4									Enter Student Name	Arts	Hindi	Science	Math					Sagar
5									To****a	55	2	4	8					Toran
6																file names		To****a
7																test sheet 2		Maya
8																test sheet 3
9																test sheet 4
10																test sheet 5
11		I have raw data in OTHER excel file named test sheet 2. The yellow table of this test sheet 1 displaying data from that sheet (test sheet 2) as per formula. I am having data in multiple sheets named test sheet 3,4,5,6.... and so on. I want that when I drag this formula down, the file name in formula should change automatically to test sheet 3,4,5.... and so on to display data from different sheets at same time. I can write file names in this test sheet 1 if needed for reference.														test sheet 6
12																test sheet 7
13																test sheet 8
14																test sheet 9
15																test sheet 10
16																test sheet 11
17																test sheet 12
18																test sheet 13
19																test sheet 14
20																test sheet 15
21
22
Sheet1

Cell Formulas
Range	Formula
K5:N5	K5	=INDEX('C:\Users\DELL\Desktop\[TEST SHEET 2.xlsx]Sheet1'!$D$5:$G$8,MATCH($J5,'C:\Users\DELL\Desktop\[TEST SHEET 2.xlsx]Sheet1'!$C$5:$C$8,0),MATCH(K$4,'C:\Users\DELL\Desktop\[TEST SHEET 2.xlsx]Sheet1'!$D$4:$G$4,0))

Cells with Data Validation
Cell	Allow	Criteria
J5	List	=$S$4:$S$7

 

[johnnyL]

I'm not that good with formulas but maybe a formula in K5 like:
=INDEX('C:\Users\DELL\Desktop\[TEST SHEET " & Row(2:2) & ".xlsx]Sheet1'!$D$5:$G$8,MATCH($J5,'C:\Users\DELL\Desktop\[TEST SHEET " & Row(2:2) & ".xlsx]Sheet1'!$C$5:$C$8,0),MATCH(K$4,'C:\Users\DELL\Desktop\[TEST SHEET " & Row(2:2) & ".xlsx]Sheet1'!$D$4:$G$4,0))

Then drag that down.

 

[rival36]

its not working sir

 

[johnnyL]

One last try from me

=INDEX(Indirect("'C:\Users\DELL\Desktop\[TEST SHEET " & Row(2:2) & ".xlsx]Sheet1'!$D$5:$G$8"),MATCH($J5,Indirect("'C:\Users\DELL\Desktop\[TEST SHEET " & Row(2:2) & ".xlsx]Sheet1'!$C$5:$C$8,0"),MATCH(K$4,Indirect("'C:\Users\DELL\Desktop\[TEST SHEET " & Row(2:2) & ".xlsx]Sheet1'!$D$4:$G$4,0")))

 

[rival36]

now perfect. thanks

 

[johnnyL]

Glad to help.