How to have a variable cell reference across sheets?

[Guest]

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.

 

[dewsbury]

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.

 

[kevindmorgan]

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!

 

[Guest]

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.