Excel drop down - Very Urgent**

M

Mkeerthi

New Member
Joined
Nov 23, 2017
Messages
17
I have a data in Sheet1 with Coloum A1 Name: Class X then A2,A3,A4 are the names of students in Class X and Column B1 Name: Class IX and then B2, B3, B4 are the names of students in Class IX and then Coloum C1 name: Class VIII and then C2,C3,C4 are the names of students in Class VIII.

Now in Sheet 2 I want to create a drop down in Column A:Class and Column B:Student Name such a way that if I select Class X in the drop down of Column A it should show the only the student names of Class X in the drop down of Column B. Is this Possible?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Hi,

In column A use Data Validation list with source :

=Sheet1!$1:$1

In column B use Data Validation list with source :

=OFFSET(Sheet1!$A$2:$A$1000,0,MATCH($A1,Sheet1!$1:$1,0)-1)

Assuming there's no more than 1000 students in 1 class
 
Last edited:
Upvote 0
[TABLE="width: 708"]
<colgroup><col span="5"><col><col><col span="4"></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]class[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name1[/TD]
[TD]C1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name2[/TD]
[TD]C2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name3[/TD]
[TD]C3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]class[/TD]
[TD]C2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name4[/TD]
[TD]C1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name5[/TD]
[TD]C2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Count of name[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name6[/TD]
[TD]C3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name7[/TD]
[TD]C1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name11[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name8[/TD]
[TD]C2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name14[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name9[/TD]
[TD]C3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name17[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name10[/TD]
[TD]C1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name11[/TD]
[TD]C2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name20[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name12[/TD]
[TD]C3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name5[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name13[/TD]
[TD]C1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]name8[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name14[/TD]
[TD]C2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name15[/TD]
[TD]C3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name16[/TD]
[TD]C1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name17[/TD]
[TD]C2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name18[/TD]
[TD]C3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name19[/TD]
[TD]C1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name20[/TD]
[TD]C2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]a pivot table lists who is in the selected class[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
[TABLE="width: 192"]


<colgroup><col width="64" style="width: 48pt;" span="3">
<tbody>[TR]

[TD="class: xl63, width: 64, bgcolor: transparent"]Class X[/TD]

[TD="class: xl63, width: 64, bgcolor: transparent"]Class XI[/TD]

[TD="class: xl63, width: 64, bgcolor: transparent"]Class VIII[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]Arul[/TD]

[TD="class: xl63, bgcolor: transparent"]Ram[/TD]

[TD="class: xl63, bgcolor: transparent"]Samuel[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]Arun[/TD]

[TD="class: xl63, bgcolor: transparent"]Mohan[/TD]

[TD="class: xl63, bgcolor: transparent"]Sai[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]Vijay[/TD]

[TD="class: xl63, bgcolor: transparent"]Rao[/TD]

[TD="class: xl63, bgcolor: transparent"]Hari[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]Vinay[/TD]

[TD="class: xl63, bgcolor: transparent"]Murthy[/TD]

[TD="class: xl63, bgcolor: transparent"]Ravi[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]Venkatesh[/TD]

[TD="class: xl63, bgcolor: transparent"]Bharat[/TD]

[TD="class: xl63, bgcolor: transparent"]Reddy[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]Mohan[/TD]

[TD="class: xl63, bgcolor: transparent"]Venky[/TD]

[TD="class: xl63, bgcolor: transparent"]Rajendra[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]Karthik[/TD]

[TD="class: xl63, bgcolor: transparent"] [/TD]

[TD="class: xl63, bgcolor: transparent"]Rasool[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]Sudharsan[/TD]

[TD="class: xl63, bgcolor: transparent"] [/TD]

[TD="class: xl63, bgcolor: transparent"]Teja[/TD]

[/TR]


</tbody>[/TABLE]
 
Upvote 0
But this is just a sample of data and my requirement is much more there by I am looking for only drop down as solution.
 
Last edited:
Upvote 0
I am going through the link. Hope this should be the solution. Will come back I meet my requirements with a thank you note.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Count names based on enrollment
Replies
2
Views
235
paulkc
P
GMC The Macro Man
  • Solved
Searchable Drop down List
Replies
4
Views
377
KevCarter
KevCarter
K
  • Question Question
Return column header name for item located in an array
Replies
4
Views
248
DanteAmor
DanteAmor
A
  • Question Question
Excel formula please
Replies
4
Views
119
JoeMo
J
A
  • Question Question
Can someone help find the Excel formula
Replies
5
Views
275
Cubist
Cubist

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top