Auto fill formula to last row, formula references another sheet

I have a formula that converts a "Yes" or "No" row to a "1" or "0".

This formula is in another sheet.

So I have one column "Yes", "No", "No", "No", "Yes", "Yes" etc.

and in another sheet I have the equivalent 1,0,0,0,1,1

I want to drag down the formula in the first cell to apply to all the rows in the original worksheet.

This means I have to refer to the original worksheet for the last row number.

How do I quickly do this without having to click and hold down until it reaches the last row?

Something like this:

Please Login or Register  to view this content.

A1:A - where formula should be added
Sheet1 - Sheet name where yes or No is found
"=IF(Sheet1!B1=""yes"",1,0)" - Your formula

How do I do this without using VBA?