Excel gurus: how do I reference a cell based upon the value in another cell?

[Tofer]

I'm not even sure how to phrase the question. Here's what I want to do:

In cell A1: 4

in cell B1: the value that is in cell A4

If I change cell A1 to 5, I want B1 to now hold the value of A5

Thanks!

-Tofer

[Filliam H. Muffman]

=A1

=$A$1 if you want to copy and paste or fill down/right into other cells to take the same value.

Edit: putting "$" in the cell is absolute referencing.

[timg]

Look into the OFFSET worksheet function. It should be able to do what you want.

[Tofer]

timg wrote:
Look into the OFFSET worksheet function. It should be able to do what you want.

Thanks -- OFFSET will do the trick.

[N-OS X-tasy!]

Filliam's suggestion will also "do the trick" in what is probably the simplest manner possible.

[timg]

N-OS X-tasy! wrote:
Filliam's suggestion will also "do the trick" in what is probably the simplest manner possible.

Actually, it won't do what Tofer is looking for.

[Tofer]

timg wrote:
[quote=N-OS X-tasy!]
Filliam's suggestion will also "do the trick" in what is probably the simplest manner possible.

Actually, it won't do what Tofer is looking for.
Right -- Filliam's suggestion just simply puts a value in a cell, but I need to use the value of that cell to reference another cell. In my example from above:



As you can see, B1 has the value "=OFFSET(A1,A1,0)". This tells B1 to look N rows down from A1, where N is the value of A1 (in this case, 4). Therefore, B1 becomes 123. If I change the value in A1 to 5, B1 magically becomes 456.

Why did I need this little trick? I had a giant list of data, but I only needed every 19th row. It was easy to set up an OFFSET that included ROW()*19 to get every 19th row. Thanks, timg!

[timg]

you're welcome.