Can you offset a formula in a cell reference?

[Dgenty]

Good Afternoon,

I am sure there must be a way to do this but I cant find the answer anywhere.

I am wanting to offset a formula in a cell

So for example if:

Sheet1!C5 ="Sheet2!C5"

I want a macro to make the Sheet2 reference move 5 columns along to Sheet2!H5 while keeping the original Sheet1 reference in the same place.

Can this be done?

Offset just seems to the selected cell where i want to move the formula in the cell 5 columns along.

Regards

 

[Special-K99]

in Sheet1!C5
=INDIRECT("Sheet2!"&CHAR(65+(COLUMN()-3)+7)&"5")

 

[Dgenty]

in Sheet1!C5
=INDIRECT("Sheet2!"&CHAR(65+(COLUMN()-3)+7)&"5")

Thanks for this,

Is there a way to do it in VBA so I can choose to move over 5 columns when I assign it to a button?