Offseting a cell reference to a second worksheet

Nico123

New Member
Joined
Aug 18, 2005
Messages
7
I have a range of cells on sheet1 referencing a second worksheet so that cell A1 on sheet 1 contains;
Code:
   =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
Code:
 =OFFSET(Sheet2!M105,99,0).

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

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Aug 18, 2005
Messages
7
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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.

Code:
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
 

Forum statistics

Threads
1,181,658
Messages
5,931,270
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top