+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    08-14-2010
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2003
    Posts
    2

    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?
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    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)
    Ben Van Johnson

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    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. #4
    Registered User
    Join Date
    08-14-2010
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2003
    Posts
    2

    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. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    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. #6
    Registered User
    Join Date
    08-26-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    1

    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. #7
    Registered User
    Join Date
    05-17-2014
    MS-Off Ver
    Excel 2003
    Posts
    1

    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. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    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.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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