One way :
Enter your formula as described above =Sheet1!A1 then pull down the handle at the lower right corner of the cell where you entered the formula as far as needed
Using IF with range will not work
As for the cells containing a 0, is there a formula in them, and which is it?
You can eventually check " do not show zero values " in the XL options

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post