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

 

[PaddyD]

untested, but indirect should do it:

=Offset(indirect(a1), 0, 10, , )

paddy

 

[Mark O'Brien]

That's the first thing I tried. It's returning a #REF!.

The second thing I tried was coming here. (actually, that's not true I've been looking through the help file as well)

 

[Aladin Akyurek]

On 2002-11-04 17:13, Mark O'Brien wrote:
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

=OFFSET(EVAL(FORMULATEXT(A1)),0,10,,)

No VBA, just MOREFUNC...

Aladin

 

[PaddyD]

=OFFSET(INDIRECT(A1),0,10,,)

tested now works for me ?!? (but only if Sheet2!C23 is entered as text. The only way I've been able to replicate the #ref error is if I enter

=Sheet2!C23

in A1,

Paddy
This message was edited by PaddyD on 2002-11-04 17:30

 

[Dave Patton]

untested but consider

=EVAL("Offset(C23, 0, 10, , )")


Eval is part of Morefunc.

 

[zacemmel]

Morefunc IS VBA, is it not? ;p

 

[PaddyD]

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

No

 

[Mark O'Brien]

=OFFSET(EVAL(FORMULATEXT(A1)),0,10,,)

No VBA, just MOREFUNC...

Aladin

Gah! MOREFUNC! I don't have it. (at least I don't think I do) Oh well, if that's the best there is then I'm just going to have put in the references for those 5 cells. (I was being lazy in an educational way)

=OFFSET(INDIRECT(A1),0,10,,)

tested now works for me ?!? (but only if Sheet2!C23 is entered as text. The only way I've been able to replicate the #ref error is if I enter

=Sheet2!C23

Yeah, that's the way INDIRECT should work, which is not what I was wanting. Oh well. If I was really desperate I would put the "Sheet2!C23" in another column I suppose and INDIRECT everything.

Thanks guys.

I should just use VBA to PO my boss. (and Aladin )

 

[Aladin Akyurek]

On 2002-11-04 17:26, PaddyD wrote:
=OFFSET(INDIRECT(A1),0,10,,)

tested now works for me ?!? (but only if Sheet2!C23 is entered as text. The only way I've been able to replicate the #ref error is if I enter

=Sheet2!C23

in A1, in which case A1 has the desired value already!

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