Noob Help Please

[MRT The Duck]

I am hoping someone can help.

It seems like such an easy thing to do and I just can't find the answer anywhere.

So, I would like to search a range of cells F2:F6 (APPLICATION) for the word "Yes"

The word will be returned in multiple cells and I would like to copy the B2:B6 corresponding cell to another location. I have set out as best I can an example below

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Lead Name[/TD]
[TD]Source[/TD]
[TD]Sub Source[/TD]
[TD]Sub Source Company[/TD]
[TD]Application[/TD]
[/TR]
[TR]
[TD]28/01/2019[/TD]
[TD]Example 1[/TD]
[TD]Self Generated[/TD]
[TD]Example Source 1[/TD]
[TD]Example Sub Source 1[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]30/10/2019[/TD]
[TD]Example 2[/TD]
[TD]Self Generated[/TD]
[TD]Example Source 2[/TD]
[TD]Example Sub Source 2[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]31/01/2019[/TD]
[TD]Example 3[/TD]
[TD]Self Generated[/TD]
[TD]Example Source 3[/TD]
[TD]Example Sub Source 3[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]12/02/2019[/TD]
[TD]Example 4[/TD]
[TD]Self Generated[/TD]
[TD]Example Source 4[/TD]
[TD]Example Sub Source 4[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]13/02/2019[/TD]
[TD]Example 5[/TD]
[TD]Self Generated[/TD]
[TD]Example Source 5[/TD]
[TD]Example Sub Source 5[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to MrExcel.
How about

Book1
	A	B	C	D	E	F	G	H
1	Date	Lead Name	Source	Sub Source	Sub Source Company	Application
2	28/01/2019	Example 1	Self Generated	Example Source 1	Example Sub Source 1	Yes		Example 1
3	30/10/2019	Example 2	Self Generated	Example Source 2	Example Sub Source 2	No		Example 3
4	31/01/2019	Example 3	Self Generated	Example Source 3	Example Sub Source 3	Yes		Example 5
5	12/02/2019	Example 4	Self Generated	Example Source 4	Example Sub Source 4	No
6	13/02/2019	Example 5	Self Generated	Example Source 5	Example Sub Source 5	Yes
02

Cell Formulas
Range	Formula
H2	=IFERROR(INDEX($B$2:$B$6,AGGREGATE(15,6,(ROW($B$2:$B$6)-ROW($B$2)+1)/($F$2:$F$6="yes"),ROWS(H$2:H2))),"")

 

[Peter_SSs]

Welcome to the MrExcel board!

Another option.
- Put AutoFilter on column F and Filter for 'Yes'
- Manually select all of what you see below the column B heading and Copy/Paste it to wherever you want
- Remove AutoFilter (or Filter for Select All)

 

[MRT The Duck]

Hi & welcome to MrExcel.
How about

	A	B	C	D	E	F	G	H
Date	Lead Name	Source	Sub Source	Sub Source Company	Application
Example 1	Self Generated	Example Source 1	Example Sub Source 1	Yes	Example 1
Example 2	Self Generated	Example Source 2	Example Sub Source 2	No	Example 3
Example 3	Self Generated	Example Source 3	Example Sub Source 3	Yes	Example 5
Example 4	Self Generated	Example Source 4	Example Sub Source 4	No
Example 5	Self Generated	Example Source 5	Example Sub Source 5	Yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]28/01/2019[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]30/10/2019[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]31/01/2019[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12/02/2019[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]13/02/2019[/TD]

[TD="align: right"][/TD]

</tbody>

02​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IFERROR(INDEX($B$2:$B$6,AGGREGATE(15,6,(ROW($B$2:$B$6)-ROW($B$2)+1)/($F$2:$F$6="yes"),ROWS(H$2:H2))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Amazing, thank you! I had to play around with it as there are actually 1000 bits of data to look through but works perfectly.

 

[MRT The Duck]

Welcome to the MrExcel board!

Another option.
- Put AutoFilter on column F and Filter for 'Yes'
- Manually select all of what you see below the column B heading and Copy/Paste it to wherever you want
- Remove AutoFilter (or Filter for Select All)

Thanks Peter, Unfortunately the AutoFilter plays havoc with the sheet for some reason. If i use it I am then unable to enter data in certain cells

 

[Peter_SSs]

Thanks Peter, Unfortunately the AutoFilter plays havoc with the sheet for some reason. If i use it I am then unable to enter data in certain cells

That doesn't make any sense to me. The suggestion involved applying AutoFilter copying what you wanted and removing Autofilter. I cant see how that could possibly affect what you subsequently want to enter. Never-the-less you seem to have something that you are happy with & that is the main thing.

 

[MRT The Duck]

That doesn't make any sense to me. The suggestion involved applying AutoFilter copying what you wanted and removing Autofilter. I cant see how that could possibly affect what you subsequently want to enter. Never-the-less you seem to have something that you are happy with & that is the main thing.

I know, I didn't create the form so don't know the reasons behind the auto filter messing around with things.
It was the first thing i tried to be honest as it would have saved a load of hassle however every time I filter and then unfilter I am unable to type anything in the Yes or No box.. Very strange.

Thanks so much for the replies, it is much appreciated

 

[Peter_SSs]

.. every time I filter and then unfilter I am unable to type anything in the Yes or No box..

Sounds like there might be some sheet protection and/or vba code going on in your workbook. However, it seems the formula solution suggested by Fluff is meeting your needs so I guess we don't need to delve into the reasons for this particular behaviour any further.