students' information thr their serila no.s

[arif]

I have a sheet with two sheets
first sheet contains informations about students from different class
now i want such a formula that
if i type only serial no. whole details given in second sheet should displayed automatically how is that possible
suggest some magic
If worksheet not found then see this
No. Name Village Class Rank
1 Manoj A 1 5
2 Atul B 2 6
3 Veera C 4 7
4 Ganesh D 8 2
I want such a formula that if i press only whole details should be dispalyed
like I prss : 1
Then
Name : Ganesh
Village : A
Class : 1
Rank : 5
Plz show some magic

 

[Asheesh]

Hi Arif..

Check the attached

Hope this helps...

 

[arif]

Hi Arif..

Check the attached

Hope this helps...

What a magic but is there no any way to do this without data validation or pivot table

and what if i want to add more details like serial no.s or names and other details

 

[srinidhi]

Check this out, reference is given to the entire column from B:F , so when you add in more serial no.s the data will be automatically update in the next sheet. If you add any more column say G,H,I, then in the formula just replace F with the last column of your data.

 

[Deepak]

Hi,

As you already set the table {that's nice} ,so use the below universal formula.

=INDEX(Table1,MATCH($F$4,Table1[Roll No.],0),MATCH($E5,Table1[#Headers],0))

Just change the red cells as required & drag the formula or check with attached one.

 

[Asheesh]

@arif - data shown in tab "list" is in table format so everytime you add a line item or delete..the table ranges will automatically change..

Regarding the data validation..I have defined a name range "RngRoll" press CTRL + F3 to check...as explained above..everytime you add a line item..it will auto-incorporate the changes..

If you do not want to use data validation you can try what Deepak says in his comment number 5..