# Formula Help

#### Mark O'Brien

##### MrExcel MVP
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

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

##### MrExcel MVP
untested, but indirect should do it:

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

#### Mark O'Brien

##### MrExcel MVP
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)

##### MrExcel MVP
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...

##### MrExcel MVP

=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,

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

#### Dave Patton

##### Well-known Member
untested but consider

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

Eval is part of Morefunc.

#### zacemmel

##### Well-known Member

Morefunc IS VBA, is it not? ;p

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

No

#### Mark O'Brien

##### MrExcel MVP
=OFFSET(EVAL(FORMULATEXT(A1)),0,10,,)

No VBA, just MOREFUNC...

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 )

##### MrExcel MVP
=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!

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.

Replies
4
Views
176
Replies
0
Views
54
Replies
1
Views
45
Replies
9
Views
153
Replies
3
Views
212

1,148,179
Messages
5,745,201
Members
423,932
Latest member
pablo2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back