Thanks for the suggestion. As I said in my last post, I simplified the
example from the original model. There are actually 10 sets of data on page 1
similar to the set I showed in the example, so the actual reference in A1 may
be in row 1, row 10, row 20, etc. on sheet 1. Also for HLOOKUP to work, the
data must be in ascending order, and it won't be in my model. But you may be
on the right track if I use an INDEX/MATCH statement instead of the HLOOKUP.
I will need to figure out how to extract the variable cell reference data
from cell A1, Sheet 2 to populate the row and column info in the INDEX/MATCH
statement in cell A2, Sheet 2. I can embed an OFFSET formula in the
INDEX/MATCH statement, since the number of rows for the offset will be
consistant within each set of data. I'll play around with that to see if I
can make it work, and post a response at a later date.

"JMB" wrote:

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?