Indirect function

Jac

New Member
Joined
May 5, 2002
Messages
37
And while I'm at it... I like the INDIRECT function, but have got myself stuck. I want to INDIRECT to a cell reference but don't want to name it as a range. Therefore in my formula I am using INDIRECT ($B$2) where B2 contains the text Sheet1!a14. I can't figure out how to use the address function to give an address of a cell on a different sheet as the result of the formula, not the actual formula. Suggestions? Thanks!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
not quite clear - how about posting the formulas you've been trying & an example of what result you want.

Paddy
 

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
I'm not clear what you're after either, but it looks like you're trying to get the cell ref on Sheet1 that is being referred to by the text, ie A14. If that's the case, why not butcher the text directly instead of analysing the results of indirect.

=RIGHT(B2,LEN(B2)-FIND("!",B2))

will return A14. Is that what you want?

Richard


_________________
This message was edited by RichardS on 2002-08-29 22:18
 

Jac

New Member
Joined
May 5, 2002
Messages
37
OK.My formula is as below.
=OFFSET((INDIRECT($B13)),$B14,C$2)

So it's the INDIRECT ($B13) bit I want to play around with. I want the OFFSET to come off a cell from another sheet, which is Sheet1!A4, say. The reason I have set up this OFFSET function like this is that I have 10 tables all the same, and I can fill using this formula and change where I'm looking up just by changing B13, which is the cell which holds the offset reference.
Unfortunately I can only get this INDIRECT to work if I manually type in cell B13, and I have to TYPE Sheet1!A4. This is not a formula, it is text. Obviously it doesn't update when I add/remove rows/columns in Sheet1 which is a pain. What I'm really wanting is a formula which gives as its result, a formula. This will then be used as the INDIRECT reference for the offset function. I have tried playing around with ADDRESS but can't really figure anything out. Does this make it clearer?
 

DBA

Board Regular
Joined
May 28, 2002
Messages
100
Hi Jac,

(If you work at an airport I don't mean "Hi Jac" as the aeroplane sense)

Instead of using the INDIRECT($B13) have you thought about naming the starting range - ie Name Sheet2!A1 as "StartPoint" or something similar then use =OFFSET(StartPoint,$B14,C$2)

Does this help or just shift the problem sideways?

regards,

DBA
 

Jac

New Member
Joined
May 5, 2002
Messages
37
I'm trying not to name ranges as I have literally dozens of named ranges already. These offset points are for these tables only and there are 10 tables so far and spawning more. I suppose in simple terms I'm looking for a way to reference to the FORMULA in a cell, or else make the output be a formula. Or else, go home. Actually that last sounds good. :biggrin:
 

Forum statistics

Threads
1,147,622
Messages
5,742,205
Members
423,711
Latest member
luisfreitas

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