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?