Excel 2003 Want Offset Reference to be Contents of Cell

[mot]

I have done a Scenario and they list the cell address (example R2C35) in the Report - the cell has the text "R2C35" in it.

I want to get the contents of a cell 2 columns to the left of Cell R2C35.

So what would I use for the Reference Cell in the Offset formula?

[majosum]

Does the cell address change or is R2C35 always the value in the cell? Do you always want to return the value 2 columns to the left of the cell address?

[mot]

The value of the cell could change AND the offset could change.

What I need is Offset (R2C35,...) its just that R2C35 is text in another cell.

[majosum]

How do you know what the offset will change by?

[icestationzbra]

let us say that cell H3 has the result of your Scenario, and it is populated with the value R2C35.

put this formula in cell H4:

Please Login or Register  to view this content.

it will return the value that exists in the cell R2C33 - at the intersection of 2nd Row and 33rd Column. change the highlighted "2" to any other number to get a different "offset".

[Tony Valko]

Here's another one.

A1 = R2C35

=INDIRECT(LEFT(A1,SEARCH("c",A1))&MID(A1,SEARCH("c",A1)+1,5)-2,0)

[mot]

I don't know how I missed it, but where

R2C1 = R2C35
OFFSET ( INDIRECT(R2C1), 0, -2)

brings back the contents of R2C33

Sorry but I don't know how I missed this since this was the first thing I tried. I'll have to check the spreadsheet at the office on Monday and see if I can determine how I got things confused.

Sorry again,
Tom

[Tony Valko]

Try it like this...

A1 = R2C35

=OFFSET(INDIRECT(A1,0),0,-2)

[mot]

Tony,

Interesting your version of the solution.

I first did it using A1 references so the TRUE default 2nd parameter of INDIRECT was not needed.

Then I changed the cell references to RC style and the formula still worked without the FALSE 2nd parameter.