Indirect function

[Jac]

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!

 

[PaddyD]

not quite clear - how about posting the formulas you've been trying & an example of what result you want.

Paddy

 

[RichardS]

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]

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]

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]

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.