Offset in another sheet

[Guest]

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

 

[Guest]

See if this helps you get started or gives you an idea or two:
On Sheet1, put some identifiying text into A1, C1, A2 and C2. Just so
you'll know which one you are looking at on Sheet2.

On Sheet2, in B1 (or any cell other than B2 or B3 for now), type
Sheet1!A1
Do not put an equal sign in front of it - type it just like I did above.

In Sheet2, cell B2 put:
=INDIRECT(B1)
in Sheet2, cell B3 put:
=OFFSET(INDIRECT(B1),0,2)

Change the entry in Sheet2!B1 to
Sheet1!A2
and watch what happens. Change it back to Sheet1!A1 ... give you any ideas?
I hope.

 

[Guest]

Thanks, very helpful.
Mike

See if this helps you get started or gives you an idea or two:
On Sheet1, put some identifiying text into A1, C1, A2 and C2. Just so
you'll know which one you are looking at on Sheet2.

On Sheet2, in B1 (or any cell other than B2 or B3 for now), type
Sheet1!A1
Do not put an equal sign in front of it - type it just like I did above.

In Sheet2, cell B2 put:
=INDIRECT(B1)
in Sheet2, cell B3 put:
=OFFSET(INDIRECT(B1),0,2)

Change the entry in Sheet2!B1 to
Sheet1!A2
and watch what happens. Change it back to Sheet1!A1 ... give you any ideas?
I hope.