Indirect relative to the referenced cell

[Immortalis]

Hi all, 

I apologize if I posted this twice, the first time I wasn't sure if it posted or not.

I've been searching the net and reading articles for about a week now and can't find the answer to this... or, perhaps I did find the answer but didn't understand it. One never knows.

Basically, here's an example of what I'm trying to do.

In A1 I want to use INDIRECT to reference another cell.

So let's say INDIRECT("R[3]C[4]") but I want it referencing "R[3]C[4]" from cell B5 and not A1.

I know, your first thought is,  "Why not just use INDIRECT("R[8]C[9]") from A1?

Well, for what I'm trying to do, that would create a lot more work and I can't even begin to describe what I'm trying to do. I'm not an excel expert and my whole idea is confusing to me so I'm just doing a piece at a time. Lol.

So, if this is possible, I'd greatly appreciate the help and if it's not possible, well I thank you for your time, just as well. :)

[#2 · Accepted solution]

@Immortalis It is a volatile function so it might slow down your worksheet if you have 10,000 of these, but how about =INDIRECT(OFFSET(A1,3,4,1,1)) in cell A1?

The Offset function is moving from the current cell (A1), down 3 rows, over 4 columns, and choosing a range that is one row by one column tall. If E4 contains the test J1, then this formula will return the value from J1.

[#3]

@Bill Jelen Thank you for your response. Yes, because of it's volatility I didn't want to use the OFFSET function but since there have been no solutions, it seems I have no choice.

It's a little disappointing but after the time I spent trying to solve it with no luck, I'm actually not surprised it can't be done the way I'd like.

No worries. Thank you very much! :)