# Excel 2007 : How to return a cell reference instead of a cell value

1. ## How to return a cell reference instead of a cell value

Does anyone know how to return the reference of a cell instead of it's value?
Specifically my problem is as follows:
In my spreadsheet, cell O4 references and returns the value from cell C4. In cell P4, I want to return the value from the cell that's one row down and one column left of cell C4. So I need cell P4 to call the cell reference instead of the cell value of C4, then manipulate the cell location to go one down and one to the left, and return that cell's value .. which should be the value of cell B5 = 18. So, cell P4 should return 18. Is there a way to do this?

2. ## Re: How to return a cell reference instead of a cell value

Syntax

OFFSET(reference,rows,cols)
Reference is the reference from which you want to base the offset.
Rows is the number of rows, up or down, that you want. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).
Cols is the number of columns, to the left (negative) or right (positive)

3. ## Re: How to return a cell reference instead of a cell value

As protonLeah says

in P4
``Please Login or Register  to view this content.``

4. ## Re: How to return a cell reference instead of a cell value

Thanks protonLeah and Marcol.

That does solve the second part of the problem. I should've been more specific on the first part. I want P4 to automatically look inside O4 and determine "C4" as the cell reference. And then I can use the offset function.
So instead of using the formula offset(C4,1,-1), in which case I have to manually type in C4, I want it to be more like offset(find the location O4 is looking at, 1,-1).

Any way to do this?

Thanks again .

5. ## Re: How to return a cell reference instead of a cell value

Could you perhaps just use:

=INDEX(\$A\$5:\$M\$5,MATCH(\$O4,\$B\$4:\$N\$4,0))
copied down

6. ## Re: Excel 2007 : How to return a cell reference instead of a cell value

Hi,

You can use a relative reference using R1C1 notation (see help snippet below). You'll have to turn the R1C1 reference style on first - see the last paragraph of the help snippet. NOTE: In Excel 2007 the "File Tab" is the Office Button (at the top left of the window), and Options is accessed from the "Excel Options" button at the bottom. When you turn R1C1 reference style on and off, all your formulas convert to and from R1C1 style.

<snippet>

From Excel Help, Overview of formulas:

The R1C1 reference style

You can also use a reference style where both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful for computing row and column positions in macros. In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number.

Reference

Meaning

R[-2]C A relative reference to the cell two rows up and in the same column
R[2]C[2] A relative reference to the cell two rows down and two columns to the right
R2C2 An absolute reference to the cell in the second row and in the second column
R[-1] A relative reference to the entire row above the active cell
R An absolute reference to the current row

When you record a macro, Excel records some commands by using the R1C1 reference style. For example, if you record a command, such as clicking the AutoSum button to insert a formula that adds a range of cells, Excel records the formula by using R1C1 style, not A1 style, references.

You can turn the R1C1 reference style on or off by setting or clearing the R1C1 reference style check box under the Working with formulas section in the Formulas category of the Options dialog box. To display this dialog box, click the File tab.

<end snippet>

7. ## Re: Excel 2007 : How to return a cell reference instead of a cell value

Hi Neurogirl,

I have effectively the same problem as you and this is the only post I can find that discusses it. In my sheet cell R1 contains a vlookup which returns a value from a cell in a table. I want to return the 7 table values below it in cells R2 to R8. The obvious way (to me) is to reference the cell **referenced** by the vlookup in R2..8 but I cannot find a way of doing this. Were you able to solve your problem?

8. ## Re: Excel 2007 : How to return a cell reference instead of a cell value

@Baxy

This thread is very old so probably you will not get a reply from OP.
Moreover it is against the forum rules also to ask question in threads of other members.
So if you have any question, you can start your own thread by providing details along with a sample workbook.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1