Find a value in a column and bring back a corresponding value

[levans_1986]

Hello
I want to populate a column with survey numbers from another worksheet. However I only want the survey number to appear if the answer to the question is yes. In worksheet 1 I have a column with yes/no in. There is another column with the survey number in. On worksheet 2 I have a column for survey number but I only want it to appear if the answer is yes in the previous worksheet.

I have tried using if(and) however it maybe that the answer of yes doesnt appear for many rows so my nested if is getting very large! Also I dont want it to bring back a survey number twice.

Does anyone have any ideas?

Many thanks
Laura

 

[Stiuart_W]

There's no reason for nested Ifs if you are only checking the one column. I suggest you post some example data as you may not be explaining completely what it is you require.

 

[levans_1986]

Hi Stiuart

Apologies its my first time posting!

As an example, sheet 1 has two columns survey number and response. Response is either yes or no. Eg

<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>
[TD="class: xl65, width: 64"]SurveyNo[/TD]
[TD="class: xl65, width: 64"]Response[/TD]

[TD="class: xl65"]A1[/TD]
[TD="class: xl65"]No[/TD]

[TD="class: xl65"]A2[/TD]
[TD="class: xl65"]No[/TD]

[TD="class: xl65"]A3[/TD]
[TD="class: xl65"]No[/TD]

[TD="class: xl65"]A4[/TD]
[TD="class: xl65"]No[/TD]

[TD="class: xl65"]A5[/TD]
[TD="class: xl65"]Yes[/TD]

[TD="class: xl65"]A6[/TD]
[TD="class: xl65"]No[/TD]

[TD="class: xl65"]A7[/TD]
[TD="class: xl65"]Yes[/TD]

</tbody>

Sheet 2 has a column with survey number and then some other columns after it. In sheet 2 I want the survey number column to be automatically populated with the survey numbers from sheet 1, however only when the response is equal to yes.

does that help at all? I'm not great on vba so would be good to avoid that if possible! apologies if I'm still unclear!

thanks

laura