+ Reply to Thread
Results 1 to 4 of 4

How to have a variable cell reference across sheets?

  1. #1
    Shane Gibson
    Guest

    How to have a variable cell reference across sheets?

    Hello - I need to reference a variable row number in another sheet, based on
    the results (value) of a row in my current sheet. Does anyone know how I can
    do this?

    i know how to reference another sheets value (='Sheet1'!A1). But in this
    case, I want to reference Row N based on the value of a cell on my current
    sheet.

    The example I can come up with - which I know doesn't work, but may help
    explain what I'm getting at - is:

    ='Sheet2'!B'Sheet1'!B2

    In this case I would be trying to reference the value of B2 (lets say Sheet1
    cell B2 value is 10) - so I'd be tryint reference the value of cell "B10" on
    Sheet2. Basically, the row number in the 2nd sheet is variable depending on
    the value of a local sheet cell value.

    Does that make sense?

    Thank you for any and all help/pointers. I've spent the last 3 hours
    searching the Excel help and online tips and help - but haven't been able to
    figure out how to do this.

  2. #2
    Registered User
    Join Date
    11-04-2004
    Posts
    12
    Hi ,

    No solution for you!

    However, by co-incidence I have made a very similar query on the forum just minutes earlier.

    Hopefully someone will sort us both out.

    See my "Dynamic Flexible Cell Address" query.

  3. #3
    Registered User
    Join Date
    03-07-2006
    Posts
    34

    Index

    Name your range in the second sheet. I named mine "thetable" in this example.

    In my example, it gives the item in column 2, and the proper row for the value that is in cell B2.

    =INDEX(thetable,B2,2)

    Hope that's what you were looking for!

    Quote Originally Posted by Shane Gibson
    Hello - I need to reference a variable row number in another sheet, based on
    the results (value) of a row in my current sheet. Does anyone know how I can
    do this?

    i know how to reference another sheets value (='Sheet1'!A1). But in this
    case, I want to reference Row N based on the value of a cell on my current
    sheet.

    The example I can come up with - which I know doesn't work, but may help
    explain what I'm getting at - is:

    ='Sheet2'!B'Sheet1'!B2

    In this case I would be trying to reference the value of B2 (lets say Sheet1
    cell B2 value is 10) - so I'd be tryint reference the value of cell "B10" on
    Sheet2. Basically, the row number in the 2nd sheet is variable depending on
    the value of a local sheet cell value.

    Does that make sense?

    Thank you for any and all help/pointers. I've spent the last 3 hours
    searching the Excel help and online tips and help - but haven't been able to
    figure out how to do this.

  4. #4
    Shane Gibson
    Guest

    Re: How to have a variable cell reference across sheets?

    "kevindmorgan" wrote:
    > Name your range in the second sheet. I named mine "thetable" in this example.
    >
    > In my example, it gives the item in column 2, and the proper row for the value that is in cell
    > B2.
    > =INDEX(thetable,B2,2)


    Kevin,

    That worked perfectly. Thanks for the help.

    Here's what I did - selected my table in "Sheet2", named it "appreciation",
    then in "Sheet1", I referred to it as follows:

    =INDEX(appreciation,C20,2)

    This referenced the value of C20 on Sheet1 (say "10") and used that as the
    Row Number in the Named Table "appreciation". In this case "2" is column "B"
    - odd why we have to switch back to numerical references for column when it's
    defined as alpha by default.

    Thanks Kevin.


+ 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