Offset to a referenced cell
 

Cell B2 Sheet2 in my workbook returns the value from cell A1 Sheet1. I want
cell B3 Sheet2 to return the value from a cell offset to cell A1 Sheet1,
(e.g. 2 columns right). However, I want to be able to change the cell in
Sheet1 that cell B2 Sheet2 references (e.g. to cell A2 Sheet1). I still want
cell B3 Sheet2 to return a value offset 2 columns right, but this time to
cell A2 Sheet1. Help. Thanks

Offset to a referenced cell
 

Hi,

One possibility is to specify the address
e.g. using an initial apostrophe to format the cell as text:

In B1 Sheet 2: 'Sheet1!A1

In B2 Sheet 2: =INDIRECT(B1)

In B3 Sheet 2: =OFFSET(INDIRECT(B1),0,2)

Whenever the address in B1 Sheet 2 is changed e.g. to 'Sheet1!A2,
cells B2 and B3 refer to the new addresses.

Hth
Anthony

"wienmichael" wrote:

Cell B2 Sheet2 in my workbook returns the value from cell A1 Sheet1. I want
cell B3 Sheet2 to return the value from a cell offset to cell A1 Sheet1,
(e.g. 2 columns right). However, I want to be able to change the cell in
Sheet1 that cell B2 Sheet2 references (e.g. to cell A2 Sheet1). I still want
cell B3 Sheet2 to return a value offset 2 columns right, but this time to
cell A2 Sheet1. Help. Thanks

Offset to a referenced cell
 

ps. sorry for duplication - just seen the other post 'Offset in another
sheet' - was looking at the latest first

Anthony


"Anthony D" wrote:

Hi,

One possibility is to specify the address
e.g. using an initial apostrophe to format the cell as text:

In B1 Sheet 2: 'Sheet1!A1

In B2 Sheet 2: =INDIRECT(B1)

In B3 Sheet 2: =OFFSET(INDIRECT(B1),0,2)

Whenever the address in B1 Sheet 2 is changed e.g. to 'Sheet1!A2,
cells B2 and B3 refer to the new addresses.

Hth
Anthony

"wienmichael" wrote:

Cell B2 Sheet2 in my workbook returns the value from cell A1 Sheet1. I want
cell B3 Sheet2 to return the value from a cell offset to cell A1 Sheet1,
(e.g. 2 columns right). However, I want to be able to change the cell in
Sheet1 that cell B2 Sheet2 references (e.g. to cell A2 Sheet1). I still want
cell B3 Sheet2 to return a value offset 2 columns right, but this time to
cell A2 Sheet1. Help. Thanks