Any reason not to do a lookup since the reference in A1 will always be to row
1 of Sheet1?

=HLOOKUP(A1,Sheet1!A:C,2,0)

The offset (2 in this example) could be stored in a separate cell if it is
subject to change.


"Cuda" wrote:

Perhaps my explanation of the issue was unclear. The relative reference in A1
Sheet 2 may be changed at any time to one of the other references in row 1 of
sheet 1. The formula in cell A2 Sheet 2 must automatically calculate an
offset to whatever reference ends up in A1 Sheet 2 (I have greatly simplified
the example vs. the spreadsheet that I'm working on. The offset cell may be a
dozen rows below the original reference on sheet 1, so the simple "fill"
solution wouldn't work. I also need the formula in A2 Sheet 2 to calculate
the offset automatically without "operator intervention", once the initial
relative reference cell is chosen.)

"JMB" wrote:

Select cell A1 on sheet2. hold the mouse over the lower right corner until
it turns to a black cross. Drag the formula down the cell A2.

Or click the copy button on the toolbar, select cell A2 and click the paste
button on the toolbar.

Excel will change the formula. See excel help for absolute versus relative
references.


"Cuda" wrote:

I have a workbook with 2 sheets. I would like to create a relative reference
on the second sheet that refers to a cell on the first sheet, and then use
that reference address to populate cells on the second sheet with relative
references that are offset from the original cell on the first sheet. Here's
an example:

Sheet 1
A B C
1 Dog Cat Cow
2 Horse Pig Zebra

Sheet 2
A B C
1 Cat Cell A1 is the relative reference =
'Sheet1'!B1
2

I need a way to use the relative reference information in cell A1 on Sheet 2
to populate cell A2 Sheet 2 with the value from the cell below 'Sheet1'!B1
(in the example above, cell A2 Sheet2 will have the value "Pig", and the
relative reference 'Sheet1'!B2. Once I learn how to extract the relative
reference address information from cell A1, Sheet 2 I know I can use the
Offset formula to populate cell A2 Sheet 2. What's the best way to do this?