Paste a formula with links

C

chrispc

I have this problem with excel. Lets use this example below. I created
a formula that uses the link of Sheet 1,Cell A2 if it does not equal 0
and I have to paste it across almost 100 cells but everytime I do a
paste special, it keeps pasting the same formula. For instance: I
want Sheet 2, Cell A2 to be the link of Sheet 1, Cell A2 and be blank
if Sheet 1, Cell A2 is blank, like so
(=IF(Sheet1!A$2="","",Sheet1!A$2)); however, for Sheet 2, Cell A3, I
want the formula to be: (=IF(Sheet1!A$3="","",Sheet1!A$3)) but
everytime I paste special the formula, it keeps everything the same so
I have to manually change the cells which takes forever because there
are so many of them.

How can I paste my formula in multiple cells in Sheet 2 linking to the
corresponding multiple cells in Sheet 1 without having to go back and
change all of the cells references?

Sheet 1, Cell A2 100
Sheet 2, Cell A2 Link'Sheet1,Cell A2'
 
Top