Update cell based of powerquery result

[chircop]

I have a button to update a powerquery on Sheet 2 from another workbook. the number of rows of data will vary.
Now i want to include VB script on the button to update data on Sheet1 based off the data imported.

What I am trying to accomplish.
if Sheet2 column A has an entry that MATCHES Sheet1 column I (a calculated value if that matters)

use the corresponding data in sheet 2 column B to change the data in Sheet1 Column F

if Sheet2 column B = 5 change Sheet1 Column F to "Y"
if Sheet2 column B = 30 change Sheet1 Column F to "NONE"
if Sheet2 column B = (any other number) change Sheet1 Column F to "N"
Incidentally, i don't want any of the data changing if the Sheet2 column B does not match Sheet 1 Column F


Sample Data Sheet 1

[TABLE="width: 319"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 319"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column F[/TD]
[TD]Column I (calculated resulting value)[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]Test Data 1[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Test Data 2[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]Test Data 3[/TD]
[/TR]
[TR]
[TD]NONE[/TD]
[TD]Test Data 4[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]Test Data 5[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]Test Data 6[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Test Data 7[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Test Data 8[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]Test Data 9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 Sample Data
[TABLE="width: 156"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]Test Data 1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Test Data 3[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Test Data 4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Test Data 6[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Test Data 7[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Test Data 9[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 1 desired result

[TABLE="width: 319"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column F[/TD]
[TD]Column I (calculated resulting value)[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Test Data 1[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Test Data 2[/TD]
[/TR]
[TR]
[TD]NONE[/TD]
[TD]Test Data 3[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Test Data 4[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]Test Data 5[/TD]
[/TR]
[TR]
[TD]NONE[/TD]
[TD]Test Data 6[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]Test Data 7[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Test Data 8[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Test Data 9[/TD]
[/TR]
</tbody>[/TABLE]

I know the logic just cannot figure out the best way to code it. Any assistance is appreciated.

 

[sandy666]

do everything with PowerQuery