Offset help

B

brianwa

I need help with and offset formula

I have a summary page with the following
C5=ERC!P5
C6=ERC!P12

What I want to do
Down column D I want to take that cell reference and offset it by 1ro
and 0colums (on the ERC page).

Thanks in advance
B
 
D

Debra Dalgleish

If the values in column P are unique, you could use INDEX/MATCH:

=OFFSET(INDEX(ERC!$P$1:$P$1000,MATCH(C5,ERC!$P$1:$P$1000,0)),1,0)
 
B

brianwa

Thanks Debra,

The formula worked, but there is some duplcations so I'll have to fin
another way around it.

Thanks again for your help!

B
 
D

Debra Dalgleish

You could enter the cell reference in another column, and refer to it in
the formula. For example, in B5, type: ERC!P5

In D5: =OFFSET(INDIRECT(B5),1,0)

Then, hide column B
 
A

Anders S

Two ways,

One:

- in a separate (hidden) column B, enter the references without the equal signs,
like
ERC!P5
ERC!P12

- in column C enter
=INDIRECT(B5)
=INDIRECT(B6)

- in column D enter
=OFFSET(INDIRECT(B5),1,0)
=OFFSET(INDIRECT(B6),1,0)


Two:

- enter the following user defined function in a general module in the VBA
editor
'-----
Option Explicit

Function getFormula(tCell As Range) As String
If tCell.Cells.Count <> 1 Then
getFormula = "Error"
Exit Function
End If
getFormula = tCell.Formula
End Function
'-----

- in column C enter (as in your example)
=ERC!P5
=ERC!P12

- i column D enter
=OFFSET(INDIRECT(MID(getformula(C5),2,2000)),1,0)
=OFFSET(INDIRECT(MID(getformula(C6),2,2000)),1,0)

HTH
Anders Silven
 
Top