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