# Add an " Offset " to a Cell Reference to a Simple =Sheet!A1 Formula

#### antfield

##### New Member
Hi

I have Sheet1 that References Sheet2 as follows;

Sheet1 in Cell A1, =Sheet2!A1
Sheet1 in Cell A2, =Sheet2!A2
Sheet1 In Cell B1, =Sheet2!B1
Sheet1 In Cell B2, =Sheet2!B2

Works Fine, but when the data on Sheet 2 for instance moves slightly ( as the data is an import ) it all goes wrong.

I wanted to add for example an offset to use, say off a New Sheet Called " OFFSET Settings "

So if the Data imported on Sheet2 say moved down one cell, I could just change the Offset from 0 to 1, so all the data on the sheet would then look at the correct cell again.

In this example if the data did shift on Sheet 2 Down by one, I would then need the formula to be - for Example
Sheet1 in Cell A1, =Sheet2!A2
Sheet1 in Cell A2, =Sheet2!A3
Sheet1 In Cell B1, =Sheet2!B2
Sheet1 In Cell B2, =Sheet2!B3

Anyone have any idea's ? I have been playing with a number of options, and likely my skill is the issue, but still have no option that works.

Anthony

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

##### Well-known Member
Hi Antfield,

Do you mean something like this?

Book2
ABCDEFGH
1Sheet1OFFSET Settings
2DogSheet21
3CatDog
4SheepCat
5GoatSheep
6MoleGoat
70Mole
80
90
Sheet1
Cell Formulas
RangeFormula
A2:A9A2=OFFSET(\$D\$1,\$G\$2+ROW()-ROW(\$A\$1),0)

#### mikerickson

##### MrExcel MVP
OFFSET is a volatile function that will slow your workbook considerably.
If your import is shifted from where you expect it to be, copying it from the wrong location and pasting it to the right location is almost as easy as entering the offest values. And won't slow your workbook.

#### antfield

##### New Member
OFFSET is a volatile function that will slow your workbook considerably.
If your import is shifted from where you expect it to be, copying it from the wrong location and pasting it to the right location is almost as easy as entering the offest values. And won't slow your workbook.
Thanks

#### antfield

##### New Member
Hi Antfield,

Do you mean something like this?

Book2
ABCDEFGH
1Sheet1OFFSET Settings
2DogSheet21
3CatDog
4SheepCat
5GoatSheep
6MoleGoat
70Mole
80
90
Sheet1
Cell Formulas
RangeFormula
A2:A9A2=OFFSET(\$D\$1,\$G\$2+ROW()-ROW(\$A\$1),0)
Excellent - Thanks

Replies
24
Views
174
Replies
4
Views
105
Replies
4
Views
193
Replies
13
Views
247
Replies
15
Views
226

1,127,567
Messages
5,625,550
Members
416,116
Latest member
Joemamasuka

### 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.

### Which adblocker are you using?

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

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