# Indirect function

#### Jac

##### New Member
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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

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

#### RichardS

##### Well-known Member
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
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
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
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.

Replies
7
Views
578
Replies
4
Views
2K
Replies
4
Views
688
Replies
5
Views
1K
Replies
3
Views
988

1,181,614
Messages
5,930,941
Members
436,767
Latest member
Langaws

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