Using an offset formula for the reference in a relative reference

[Cuda]

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?

 

[JMB]

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]

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]

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]

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]

1. As you said in your last post, cell A1 would refer to row 1 of sheet1

2. HLookup can perform approximate match and exact match lookups. Same for
VLookup and Match. See Excel help for more info.

3. I believe you could use INDIRECT, a UDF, or array formula.


INDIRECT - put Sheet1!A2 in cell A1. In A2 put =INDIRECT(A1). In A3 put
=OFFSET(INDIRECT(A1),1,0)


UDF: Paste the code into a VBA module. The default offset is 1 row and 0
columns so you can just use =MyOffset(A1). You can also specify a different
row/column offset. =MyOffset(A1, 2, 2), =MyOffset(A1,,2), =MyOffset(A1,2)
will all work (as far as syntax, the values returned will be different).


Function MyOffset(rngCell As Range, _
Optional lngRowOffset As Long = 1, _
Optional lngColOffset As Long = 0)
Dim rngSource As Range

On Error Resume Next
Application.Volatile True

Set rngSource = Range(Replace(rngCell.Formula, _
"=", "", 1, 1, vbTextCompare))
If Not rngSource Is Nothing Then
MyOffset = rngSource.Offset(lngRowOffset, _
lngColOffset).Value
Else: MyOffset = CVErr(xlErrValue)
End If

End Function


ARRAY FORMULA: This would require the data on sheet1 to be unique - so I
would probably not use it, but its up to you. Where the data is on
Sheet1!A1:C4 and A1 is linked to a cell in Sheet1!A1:C4

=INDEX(Sheet1!A1:C4,MAX((Sheet1!A1:C4=A1)*(ROW(Sheet1!A1:C4)-ROW(Sheet1!A1)+1))+1,MAX((Sheet1!A1:C4=A1)*(COLUMN(Sheet1!A1:C4)-COLUMN(Sheet1!A1)+1)))

array entered w/Cntrl+Shift+Enter. The row offset is determined by the +1
right before the second MAX function (ie "+1, MAX")

 

[Cuda]

I believe I have a workable solution, based on your suggestion to use the
INDIRECT function, as follows:

1. Format Row 1 Sheet 2 as Text.
2. With the cursor in A1 Sheet 2, hit the "=" or the "+" key, navigate to
the appropriate cell in Sheet 2, and hit enter. This establishes the link to
the cell on which to key on Sheet 1, and populates the Sheet 2 cell with the
reference, preceded by the "+" or "=". (In practice, this choice will be done
by the model user for several columns on Sheet 2, as needed.)
3. A2 Sheet 2 contains the following formula to strip off the "=" or "+" in
cell A1 Sheet 2:
=IF(LEFT(A1,1)="=",SUBSTITUTE(A1,"=",""),SUBSTITUTE(A1,"+","")). The INDIRECT
and OFFSET formulas don't work properly if the "+" or "=" are present.
4. A3 Sheet 2 has the INDIRECT(A2) function to display the contents of the
cell referred to in cell A2.
5. A4 Sheet2 has an OFFSET(INDIRECT(A2),1,0) to display the contents of the
second cell associated with the key reference on Sheet 1. There will be
several cells below this with similar OFFSET formulas using the key cell
reference in A2 to pull various data off of the appropriate row on Sheet 1 to
use in calculations on sheet 2.

The only disadvantage of this approach is that A1 Sheet 2 displays the cell
reference, rather than the data in that linked cell. It's workable, but not
pretty. I'll hide row 2 to tidy things up, since the user doesn't need to
know what's happening in that set of formulas, but I can't hide row 1 and
stiil ask the user to create the link.
I am very intrigued by the UDF alternative, but I'll need to dive into VBA a
bit deeper to be able to understand and execute that approach. Thanks a lot
for your help!