Offseting a cell reference to a second worksheet

[Nico123]

I have a range of cells on sheet1 referencing a second worksheet so that cell A1 on sheet 1 contains;

   =Sheet2!M105

All my references to sheet two were manually created, but the format of my data on sheet2 repeats itself four times (thought the vaules are different). If I could offset my references (and copy & paste?), I will save myself the drudge of manually creating 75% of the remaining references.

How can I copy a range of these cell references on sheet1 (elsewhere on sheet 1) , but offset the reference of all the references on sheet1 to sheet2 worksheet by 99 rows.

In effect, I would like an easy way to write/copy the above from Cell A1 in sheet1 to cell A400 in sheet 1 in the below format

 =OFFSET(Sheet2!M105,99,0).

Is there an easy way to do this? Any help welcome!

 

[xenou]

What happens when you type the formula just as you have showed:

=OFFSET(Sheet2!M105,99,0)

Are you saying this is not the right formula, or that you want an easy way to populate it in your sheet1 cells?

Regards.

 

[Nico123]

That is the correct formula, but I need a way to create it quickly from the existing cell links/references to sheet2.

How can I turn "=Sheet2!M105,99,0" into "=OFFSET(Sheet2!M105,99,0)" so that it brings back the value in sheet 2 without typing manually?

Ideally by copy and pasting or concatenating the existing links to an area beneath them. The 99 row offset will enable the repeating data to pick up the correct new values.

 

[xenou]

Hi:
I can't think of anything very good right now...

This is one try but to be honest I don't like it. It's doesn't have much intelligence in the routine but if you select the cells you want to "transform" it looks like it will do the job...changes the formula to add the Offset part. I think it will also change anything else in its path to a formula (add an equal sign...), so be careful only to select the cells you really want to change and save a backup. In any case, the basic idea (and all I could think of) was to remove the equal sign, work on the text strings, and put the equals sign back.

Sub test()
Dim r As Range

On Error GoTo Handler
Set r = Application.InputBox("Select the TRange to Process", Type:=8)
On Error GoTo 0

For Each c In r
    On Error Resume Next
    c.Replace What:="=", Replacement:="", LookAt:=xlPart
    On Error GoTo 0
    
    If Left(c.Value, 7) = "Sheet2!" Then
        mystring = "=Offset(" & c.Value & ",99,0)"
        c.Formula = mystring
    Else:
        mystring = "=" & c.Value
        c.Formula = mystring
    End If
Next c

Handler:
End Sub