worksheet selection based on relevant data

[rupal1234]

Hi, I want data like i if open sheet maths then it shows data of maths which is in sheet 1 like A4:F4

 

[bosco_yip]

Try,

In "Maths" sheet B2, copied across to G2 :

=INDEX(Sheet1!A$1:A$100,MATCH(MID(CELL("filename",$A1),FIND("]",CELL("filename",$A1))+1,32),Sheet1!$C$1:$C$100,0))

Regards
Bosco

 

[rupal1234]

Th

Try,

In "Maths" sheet B2, copied across to G2 :

=INDEX(Sheet1!A$1:A$100,MATCH(MID(CELL("filename",$A1),FIND("]",CELL("filename",$A1))+1,32),Sheet1!$C$1:$C$100,0))

Regards
Bosco

Thankyou so much but i want all maths data like maths is coming in C4 and C7 in sheet maths

 

[bosco_yip]

Th

Thankyou so much but i want all maths data like maths is coming in C4 and C7 in sheet maths

Then,

In " Maths" sheet B2, revised formula copied across to G2 and all down :

=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!A$1:A$100)/(Sheet1!$C$1:$C$100=MID(CELL("filename",$A1),FIND("]",CELL("filename",$A1))+1,32)),ROWS($1:1))),"")

And,

select B2:G4, copy and paste to other course sheets

Regards
Bosco

 

[rupal1234]

But its not showing any data