Formula Help

[Mark O'Brien]

Suppose Cell A1 houses the formula:

=Sheet2!C23

In Cell A2 I would like to have the value of the cell 10 places to the right of the range referenced in cell A1, to do this manually, the formula would look like this:

=Offset(Sheet2!C23, 0, 10, , )

Is there a clever way for me to pick up the the "Sheet2!C23" part of the formula in A1?

Thanks

 

[zacemmel]

Then what is morefunc exactly?

 

[Aladin Akyurek]

On 2002-11-04 17:30, zacemmel wrote:
Morefunc IS VBA, is it not? ;p

No. It's C or probably C++.

 

[Mark O'Brien]

Paddy,

The game Mark is playing is one of re-entrant formula. He wishes to enter =Sheet2!C23 once to get the value this houses and, in B1, to get the value 10 cells further from the value in A1 in whatever sheet A1-value comes from.

Aladin

Education is never a game. (but you've managed to sum up my intent nicely)


Hi ho, hi ho, it's back home I go (to reinstall WinME, yay!)

 

[zacemmel]

Gotcha. Much more efficent.

 

[PaddyD]

Using only native excel functions:

1) go to insert | name | define.
2) define offsetref as:

=GET.CELL(6,Sheet1!$A$1)

3) return the desired value with

=OFFSET(INDIRECT(RIGHT(offsetref,LEN(offsetref)-1)),0,10,,)

Paddy

EDIT:

Aladin, I guess the clue was given when Mark said:

"Suppose Cell A1 houses the formula:

=Sheet2!C23 "

...must learn to read
This message was edited by PaddyD on 2002-11-04 17:48