Something that has always bugged me in excel is the fact that if I have a formula that references a cell more than once and I want to change the cell that is being referenced I have to drag the box twice. I am sure there must be an easier way of doing this but have never stumbled across it.

As a very simple example... =IF(A1>0,A1,0)
If I wanted to change this to... =IF(A2>0,A1,0)
I would need to drag the box from A1 to A2 twice.

I want to do this by pressing a key (have tried ctrl, alt, shift) and then just dragging the reference once. Does anyone know if there is a way to perform the action as I want to?

To clarify, I realise in this case it is very easy to more the reference twice (or drag it down one cell then locked reference copy back) however if rather than 2 references there were 6 and I wanted to move them along way or across multiple sheets moving them all at once would be much easier.

For reference, the way I do it currently is by either; doing it manualy x times or using ‘find and replace’ to change them all... using the mouse would be much more preferable.

Thanks for your help.