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

antfield

New Member
Joined
Mar 28, 2006
Messages
33
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,280
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jan 15, 2007
Messages
23,902
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
Joined
Mar 28, 2006
Messages
33
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,429
Messages
5,596,078
Members
414,042
Latest member
Swiftkoala

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