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?