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

[antfield]

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

 

[Toadstool]

Hi Antfield,

Do you mean something like this?

Book2
	A	B	C	D	E	F	G	H
1	Sheet1						OFFSET Settings
2	Dog			Sheet2			1
3	Cat			Dog
4	Sheep			Cat
5	Goat			Sheep
6	Mole			Goat
7	0			Mole
8	0
9	0
Sheet1

Cell Formulas
Range	Formula
A2:A9	A2	=OFFSET($D$1,$G$2+ROW()-ROW($A$1),0)

 

[mikerickson]

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]

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]

Hi Antfield,

Do you mean something like this?

Book2
	A	B	C	D	E	F	G	H
1	Sheet1						OFFSET Settings
2	Dog			Sheet2			1
3	Cat			Dog
4	Sheep			Cat
5	Goat			Sheep
6	Mole			Goat
7	0			Mole
8	0
9	0
Sheet1

Cell Formulas
Range	Formula
A2:A9	A2	=OFFSET($D$1,$G$2+ROW()-ROW($A$1),0)

Excellent - Thanks