Formula Help

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
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 you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
untested, but indirect should do it:

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

paddy
 

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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... :biggrin:

Aladin
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

=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

Well-known Member
Joined
Feb 15, 2002
Messages
4,961
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
untested but consider

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


Eval is part of Morefunc.
 

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636

ADVERTISEMENT

Morefunc IS VBA, is it not? ;p
 

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
=OFFSET(EVAL(FORMULATEXT(A1)),0,10,,)

No VBA, just MOREFUNC... :biggrin:

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 :p )
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
 

Forum statistics

Threads
1,143,737
Messages
5,720,566
Members
422,291
Latest member
Deveshk

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top