OFFSET() question for '97

[Adam Kroger]

In the OFFSET() function, is there something that you can put ub the formula
for "reference" that returns as the cell when you do not know what the cell
address will be?

FURTHER EXPLANATION:
OFFSET(reference,rows, cols,height,width)

OFFSET(this.cell,0,5)


I want to use it in conjunctuon with the UseAsFormula() UDF & VLOOKUP()

The formula
=IF(OFFSET(this.cell,0,-5)>1,Offset(this.cell,0,-5)-2,OFFSET(this.cell,0,-5)*(-1))
will be in a cell and returned occasionally, or not at all in any of
possibly 2100 cells of my worksheet by a VLOOKUP(), if I can ever get the
USEasFormula to work with a VLOOKUP().

Thanks in advance
Adam

 

[Harlan Grove]

In the OFFSET() function, is there something that you can put ub the
formula for "reference" that returns as the cell when you do not know what
the cell address will be?

FURTHER EXPLANATION:
OFFSET(reference,rows, cols,height,width)

OFFSET(this.cell,0,5)

....

If by this.cell you mean a reference to the cell containing the formula,
then don't use OFFSET. Use INDIRECT with R1C1 addressing.

INDIRECT("RC[5]",0)

 

[Adam Kroger]

Thank you, I think that will work.

In the OFFSET() function, is there something that you can put ub the
formula for "reference" that returns as the cell when you do not know what
the cell address will be?

FURTHER EXPLANATION:
OFFSET(reference,rows, cols,height,width)

OFFSET(this.cell,0,5)

...

If by this.cell you mean a reference to the cell containing the formula,
then don't use OFFSET. Use INDIRECT with R1C1 addressing.

INDIRECT("RC[5]",0)