How can I use a cell value to reference a row?

[excelchallenged]

a5 is an integer variable from 15 to 925 in increments of 1. When a5 is 20
cell a10 (or any cell I chose) should read the contents of j20. When a5 is
825 cell a10 should read the contents of j825. The integer value in a5
defines the row of column j returned in cell a10.

[Jim Thomlinson]

Take a look at the indirect function in Excel It will allow you to set your
reference dynamically. Something like this

=INDIRECT("Sheet1!J" & A5)
--
HTH...

Jim Thomlinson


"excelchallenged" wrote:

> a5 is an integer variable from 15 to 925 in increments of 1. When a5 is 20
> cell a10 (or any cell I chose) should read the contents of j20. When a5 is
> 825 cell a10 should read the contents of j825. The integer value in a5
> defines the row of column j returned in cell a10.

[Leith Ross]

Hello Excelchallenged,

In cell A10 place the formula...
=INDIRECT("J" & A5)

Sincerely,
Leith Ross

[avveerkar]

a5 is an integer variable from 15 to 925 in increments of 1. When a5 is 20
cell a10 (or any cell I chose) should read the contents of j20. When a5 is
825 cell a10 should read the contents of j825. The integer value in a5
defines the row of column j returned in cell a10.

why not
temp=[A5]
cells(10,"A").value=cells(temp,"j").value

A V Veerkar

[Jim Thomlinson]

While indirect is a volitile function meaning that it will recalcualte every
thime the sheet calculates it will (in all likelyhood) still be more
efficient than using the on change event handler that you are proposing.
Built in functions are almost always more efficient than code. If you want to
try it though be my guest. It will work...
--
HTH...

Jim Thomlinson


"avveerkar" wrote:

>
> excelchallenged Wrote:
> > a5 is an integer variable from 15 to 925 in increments of 1. When a5 is
> > 20
> > cell a10 (or any cell I chose) should read the contents of j20. When a5
> > is
> > 825 cell a10 should read the contents of j825. The integer value in a5
> > defines the row of column j returned in cell a10.
>
> why not
> temp=[A5]
> cells(10,"A").value=cells(temp,"j").value
>
> A V Veerkar
>
>
> --
> avveerkar
> ------------------------------------------------------------------------
> avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338
> View this thread: http://www.excelforum.com/showthread...hreadid=509173
>
>