Drop Down Help

russellheidi · Oct 26, 2014

Hi All

I have a spreadsheet below. Its in a team ID order and also has Engineer Tech Numbers within.

I want to create a drop down (ie) Team S0701-706 and once selected, return the whole teams results.
Does anyone know the best forulas to use or point me in right direction?

TEAM	TECH ID	ENGINEER NAME	COMP	RFT	ETA	NFF	ON D	NPS	CSAT
S0701	FS1338	STOTHER GARRY (SAT)	98.21%	96.43%	77.45%		50.70%	50.00%	90
S0701	FS3348	HALE DAVID (LAT)	93.71%	92.38%	79.31%	45.45%	36.49%	76.47%	94.5486
S0701	FS3962	EKWOANYA CHUKS	0.00%					#N/A	#N/A
S0701	FS4051	BUNN CHAS (LAT) (RR)	91.67%	83.33%	91.11%		46.15%	100.00%	100
S0701	FS4472	HAKIN DANIEL	95.00%	95.00%	79.07%		52.00%	0.00%	85
S0701	FS4540	COX EDWARD (LAT) (RR)	94.17%	94.17%	75.00%	0.00%	34.00%	100.00%	99.1667
S0701	FS4759	JALLOH ABDUL(LAT)	94.21%	91.46%	78.54%	33.33%	47.40%	60.71%	90.557
S0701	FS4819	GIBSON BEN (LAT)	92.94%	86.25%	78.29%	50.00%	52.13%	52.63%	91.0784
S0701	FS5665	BOSS RICKY	97.51%	94.70%	77.60%	66.67%	43.48%	50.00%	94.7286
S0701			0.00%
S0702	FS1361	TUERSLEY SAM (SAT) (RR)	92.59%	92.59%	83.33%		26.32%	100.00%	98.75
S0702	FS4285	SINCLAIR LEIGH (LAT)	0.00%
S0702	FS4474	HARRIS CHARLOTTE (LAT)	90.00%	89.17%	83.19%	50.00%	29.51%	60.00%	98
S0702	FS4484	COTTON-JONES BENJAMIN (RR)	88.16%	88.16%	100.00%		25.00%	-33.33%	58.3333
S0702	FS4530	FITZGERALD LESLI(LAT)	93.26%	92.20%	83.23%	0.00%	46.11%	44.44%	93.4868
S0702	FS4935	KASOLI ISLAM (LAT)	92.24%	91.32%	81.36%	25.00%	39.61%	88.89%	96.6667
S0702	FS5579	SALLAH TARIQ (LAT)	91.67%	86.57%	83.13%	100.00%	36.17%	73.33%	93.0519
S0702	FS5656	HIRVING TAFARI (LAT)	91.84%	86.30%	62.33%	54.55%	59.69%	76.47%	98.5561
S0702	FS5677	HARPER SAM	85.07%	83.26%	71.55%	33.33%	50.89%	82.61%	95.9091
S0702	FS5712	PARMAR ALI	97.84%	92.81%	80.69%	41.67%	30.97%	81.82%	94.5455
S0702	FS5735	JOHNSON LUKE	0.00%		96.88%		0.00%
S0702	FS5774	POWELL KASSIM	0.00%					#N/A	#N/A
S0702	WL83	DANIEL FRANCIS (LAT)	91.51%	87.45%	84.06%	71.43%	42.86%	63.64%	92.0833
S0702			0.00%					#N/A	#N/A
S0703	FS1323	CUMMINGS DARREN (LAT)	94.94%	93.45%	78.13%	33.33%	49.74%	82.35%	87.2151
S0703	FS2021	WALLER NEIL (SAT) (RR)	92.22%	92.22%	66.67%		10.53%	#N/A	#N/A
S0703	FS2254	RICE PAUL (LAT)	95.37%	93.59%	79.43%	66.67%	48.18%	57.69%	88.5
S0703	FS2895	ARMSTON PAUL (SAT) (RR)	100.00%	100.00%	91.84%	100.00%	38.46%	0.00%	100
S0703	FS381	FORDHAM MARK (LAT)	92.28%	91.12%	71.65%	20.00%	46.94%	88.89%	97.3333
S0703	FS4486	BANJOKO ADEKUNLE	93.29%	91.16%	79.72%	41.67%	42.24%	57.58%	90.1273
S0703	FS5010	STRETTON SCOTT (LAT)	95.10%	93.01%	72.45%	50.00%	68.06%	66.67%	94
S0703	FS5046	MILLER SEAN (LAT)	92.40%	89.60%	88.57%	0.00%	54.61%	67.86%	95.4487
S0703	FS5084	REYNOLDS JASON (LAT) (LAT)	89.61%	84.95%	68.77%	50.00%	54.55%	68.75%	91.7949
S0703	FS5280	BISHOP DWAYNE (LAT)	86.26%	80.15%	60.29%	20.00%	54.81%	66.67%	83.3333
S0703	FS5615	PEARSON ELIOT (LAT)	93.20%	90.00%	74.56%	75.00%	38.93%	71.43%	92.3333
S0703	FS753	PEARSON JAMES (LAT)	95.22%	93.17%	57.79%	54.55%	55.07%	76.92%	93.1993
S0703	AP62	HAZAEL BYRON	0.00%					#N/A	#N/A
S0703			0.00%					#N/A	#N/A
S0704	CN52	TECLEHAIMANOT REZENE (LAT)	92.00%	88.00%				100.00%	100
S0704	FS3720	HOWARD CARL (LAT) (RR)	81.58%	78.95%	60.76%	66.67%	55.00%	66.67%	86.6667
S0704	FS5254	MALCOLM KEVIN (LAT)	92.70%	90.51%	84.55%	50.00%	37.23%	65.52%	86.7037
S0704	FS5331	JAMES THOMAS (LAT) (RR)	88.78%	82.93%	80.51%	33.33%	39.39%	92.86%	96.2143
S0704	FS5349	SKOREK TOMASZ (LAT)	91.18%	89.50%	93.15%	100.00%	40.82%	81.82%	94.1667
S0704	FS5446	KEY CRAIG (LAT)	89.05%	88.10%	65.74%	100.00%	29.17%	62.50%	95.4762
S0704	FS5452	ARUMUGATHASAN CHITHRANGHAN LAT	86.41%	84.95%	69.78%	50.00%	30.93%	66.67%	96.6667
S0704	FS5621	VASSELL ASHLEE (LAT)	85.67%	83.12%	84.04%		29.07%	68.75%	94.7619
S0704	FS5666	EVERITT GARY	89.83%	88.98%	85.40%	40.00%	32.89%	55.56%	89.0714
S0704	FS5667	MARSH DANIEL (LAT)	91.29%	88.38%	75.00%	50.00%	46.67%	70.00%	87.2321
S0704	FS5711	SIDDIQUI MUHAMMED	94.90%	91.84%	86.57%	60.00%	37.40%	62.50%	95.8333
S0704	FS5710	SETHI GAGANDEEP	85.96%	83.33%	70.95%	50.00%		70.00%	95.5556
S0704			0.00%					#N/A	#N/A
S0705	FS4546	BROWNSEA DANIEL (RR)	90.79%	89.47%	84.62%	100.00%	49.47%	80.00%	94.6667
S0705	FS4556	BLOOM BILLY (RR)	77.50%	75.00%	87.72%		64.29%	66.67%	100
S0705	FS5093	ALLEN DEREK (LAT)	90.35%	84.94%	78.97%	75.00%	53.15%	61.54%	91.7033
S0705	FS5276	DOUGGAN STUART (LAT)	89.02%	86.59%				33.33%	80
S0705	FS5332	THOMPSON JASON (LAT)	91.21%	89.58%	65.33%	87.50%	40.97%	71.43%	91.0354
S0705	FS5447	STEER CHARLES (LAT)	84.06%	82.61%	73.71%	25.00%	38.00%	93.75%	91.732
S0705	FS5659	TARUVINGA TARU	87.33%	85.67%	65.38%	50.00%	64.93%	100.00%	100
S0705	FS5668	****BURN ANDREW	90.72%	89.35%	90.46%	80.00%	50.00%	67.86%	94.9647
S0705	FS5670	AHEARN FRANCIS (LAT)	87.83%	86.09%	66.28%	0.00%	44.19%	77.78%	95.641
S0705	FS5739	SHEARING WILLIAM	0.00%		45.71%		60.00%
S0705	FS5740	ELKHOUMANI NABIL	0.00%		91.18%		58.33%
S0705	FS5741	JOHNS MATTHEW	0.00%		54.84%		66.67%
S0705			0.00%					#N/A	#N/A
S0706	FS4531	DOUGLAS-WRIGHT KIRK (LAT) (RR)	80.60%	80.60%	71.79%	100.00%	51.06%	66.67%	94.125
S0706	FS4542	COOPER ANDREW (LAT) (RR)	88.82%	87.50%	63.91%	66.67%	39.19%	70.83%	97.0066

Smitty · Oct 26, 2014

Welcome to the Board!

Give Auto-Filter a shot (on the Data tab). It will give you drop-downs on row one that you can select from to filter by selection.

HTH,

russellheidi · Oct 26, 2014

Smitty said:
Welcome to the Board!

Give Auto-Filter a shot (on the Data tab). It will give you drop-downs on row one that you can select from to filter by selection.

HTH,

Hi smitthy. I want create another sheet with all the headers in and then when I select a relevant team from drop down it populates all the engineers tech codes into that sheet automatically.

Then in I will run a vlookup from that tech number to populate results. So in theory depending on what team I pick from drop down list will populate the tech I'd which in tern will populate the information.
Cannot find anything on creating drop downs that populate more than one result. Plus the only thing I can find, the tab of the sheet has to be named the same as the name in drop down

WarPigl3t · Oct 26, 2014

There is a formula you can use. I believe it is the index match formula, but I such at that formula. I have a vba solution for you.
Using an On_Change event, you can use this code.

Code:

myLookup = Range("A1").Value
'Change "A1" to the cell that has the drop down list.
i = 2
lastRow = Range("A" & Rows.Count).End(xlup).Row
Do Until i > lastRow
    If myLookup = Range("A" & i).Value Then
        Rows(i).Hidden = FALSE
    Else
        Rows(i).Hidden = TRUE
    End If
    i = i + 1
Loop

Now here's what I assumed when I created this code. I assumed that your drop down list is somewhere on the same worksheet. You didn't say what cell the drop down list was in so I assumed it was in cell A1. You can change it in the code. I assumed that you wanted the results to hide rows that weren't a match to the drop down list. I assumed that you wanted to unhide rows that did match the drop down list selection.

If your drop down list is in a different worksheet, and you want to pull the data that matches into another worksheet, then do this in an On_Change event.

Code:

sht1 = "Sheet1"
'Sheet1 is the dataset sheet that you provided in this thread.
sht2 = "Sheet2"
'Sheet2 is the sheet with the drop down list and where you want the matches to output.
myLookup = Sheets(sht2).Range("A1").Value
'A1 is where the drop down list is so change that to the cell you actually have your drop down list in.
i = 2
ii = 2
lastRow = Sheets(sht1).Range("A" & Rows.Count).End(xlup).Row
clearContents = Sheets(sht2).Range("A" & Rows.Count).End(xlup).Row
Sheets(sht2).Range("A2:Z" & clearContents).ClearContents
Do Until i > lastRow
    If myLookup = Sheets(sht1).Range("A" & i).Value Then
        Sheets(sht2).Range("A" & ii).Value = Sheets(sht1).Range("A" & i).Value
        Sheets(sht2).Range("B" & ii).Value = Sheets(sht2).Range("B" & i).Value
        'repeat similar code to accomplish the copy pasting to all the columns.  
        ii = ii + 1
    End If
    i = i + 1
Loop

Just in case you don't know how to start an On_Change event, let me help you out. At the bottom there are tabs that have the worksheet names. Right click the worksheet and click on "View Code". A code area appears. At the top is 2 drop down lists. I believe the right drop down list is the one you need. Click it and select "On_Change". Now paste my code into the On_Change event subroutine.

etaf · Oct 26, 2014

i use this
=IFERROR(INDEX(Data_Entry!$B$2:$B$52651,SMALL(IF((Data_Entry!$E$2:$E$52651=$C$2),ROW($1:$32000)),ROW(1:1)),COLUMNS($B:B)),"")

which looks at cell C2 and then tries to match with the data on a different sheet
and returns column B for every entry that matches with C2

see sample here

Excel Workbook

A

B

C

D

1

Team

2

FIND>

S0701

3

4

5

Booked In

Collected

Status

6

7

8

9

10

11

COX EDWARD (LAT) (RR)

12

13

14

15

Cust_Search

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Formula is =COUNTIF(Last_Name,C2)=0	Abc
C2	2. / Formula is =COUNTIF(Last_Name,C2)>0	Abc
D2	1. / Formula is =COUNTIF(Post_Code,D2)>0	Abc
D2	2. / Formula is =COUNTIF(Post_Code,D2)=0	Abc
C3	1. / Formula is =C3="found"	Abc
C3	2. / Formula is =C3="Customer & Postcode NOT found"	Abc
B6	1. / Formula is =B6=0	Abc
C6	1. / Formula is =B6=0	Abc
D6	1. / Formula is =D6="in Repair"	Abc
D6	2. / Formula is =D6="Warranty"	Abc
D6	3. / Formula is =D6="no Warranty"	Abc
B7	1. / Formula is =B6=0	Abc
C7	1. / Formula is =B6=0	Abc
D7	1. / Formula is =D6="in Repair"	Abc
D7	2. / Formula is =D6="Warranty"	Abc
D7	3. / Formula is =D6="no Warranty"	Abc
B8	1. / Formula is =B6=0	Abc
C8	1. / Formula is =B6=0	Abc
D8	1. / Formula is =D6="in Repair"	Abc
D8	2. / Formula is =D6="Warranty"	Abc
D8	3. / Formula is =D6="no Warranty"	Abc
B9	1. / Formula is =B6=0	Abc
C9	1. / Formula is =B6=0	Abc
D9	1. / Formula is =D6="in Repair"	Abc
D9	2. / Formula is =D6="Warranty"	Abc
D9	3. / Formula is =D6="no Warranty"	Abc
B10	1. / Formula is =B6=0	Abc
C10	1. / Formula is =B6=0	Abc
D10	1. / Formula is =D6="in Repair"	Abc
D10	2. / Formula is =D6="Warranty"	Abc
D10	3. / Formula is =D6="no Warranty"	Abc
B11	1. / Formula is =B6=0	Abc
C11	1. / Formula is =B6=0	Abc
D11	1. / Formula is =D6="in Repair"	Abc
D11	2. / Formula is =D6="Warranty"	Abc
D11	3. / Formula is =D6="no Warranty"	Abc
B12	1. / Formula is =B6=0	Abc
C12	1. / Formula is =B6=0	Abc
D12	1. / Formula is =D6="in Repair"	Abc
D12	2. / Formula is =D6="Warranty"	Abc
D12	3. / Formula is =D6="no Warranty"	Abc
B13	1. / Formula is =B6=0	Abc
C13	1. / Formula is =B6=0	Abc
D13	1. / Formula is =D6="in Repair"	Abc
D13	2. / Formula is =D6="Warranty"	Abc
D13	3. / Formula is =D6="no Warranty"	Abc
B14	1. / Formula is =B6=0	Abc
C14	1. / Formula is =B6=0	Abc
D14	1. / Formula is =D6="in Repair"	Abc
D14	2. / Formula is =D6="Warranty"	Abc
D14	3. / Formula is =D6="no Warranty"	Abc
B15	1. / Formula is =B6=0	Abc
C15	1. / Formula is =B6=0	Abc
D15	1. / Formula is =D6="in Repair"	Abc
D15	2. / Formula is =D6="Warranty"	Abc
D15	3. / Formula is =D6="no Warranty"	Abc

uses a array formula
so Control+Shift+enter
to get {} around the formula

Smitty · Oct 26, 2014

russellheidi said:
Hi smitthy. I want create another sheet with all the headers in and then when I select a relevant team from drop down it populates all the engineers tech codes into that sheet automatically.

Take a look at Pivot Tables.

Peter_SSs · Oct 27, 2014

@Wayne (etaf)
When using Excel jeanie, please use the ‘Analyse range (Forum)’ field near the top left to restrict the number of formulas generated. There is generally no need to display multiple formulas that are basically the same, it just fills up the board and makes your post and the thread hard to read/navigate.

And as far as I can see Conditional Formatting is not relevant to this question so you could remove that tick in the jeanie interface for a post like this too.

etaf · Oct 27, 2014

@Peter_SSs
Thanks for that ,I didn't know of the conditional format tick - useful
I have found that if i edit the post after pasting - then the result has lots of *** in - so I tend to leave it , But I will take greater care in future

Peter_SSs · Oct 27, 2014

etaf said:
I have found that if i edit the post after pasting - then the result has lots of *** in

Yes, that happens if you preview or edit your post. However, most times I find I still have the code on my clipboard so can just re-paste. If not, jeanie is so quick it only takes a moment to re-generate & paste to eliminate the *s.

etaf · Oct 27, 2014

Thanks - I did not know I could delete and repost - I think when I tried that it just added the **** BUT I did not go back into excel and regenerate, so I will keep in mind
thanks again

Drop Down Help

russellheidi

New Member

Excel Facts

Smitty

Legend

russellheidi

New Member

WarPigl3t

Well-known Member

etaf

Well-known Member

Smitty

Legend

Peter_SSs

MrExcel MVP, Moderator

etaf

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

etaf

Well-known Member

Similar threads

Forum statistics

Share this page

Drop Down Help

New Member

Excel Facts

Legend

New Member

Well-known Member

Well-known Member

Legend

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock