# Formula referencing a cell using a number of another cell

1. ## Formula referencing a cell using a number of another cell

I currently have the following formula;

=OFFSET(Sheet1\$A\$1,Sheet2!\$A\$2,0)

I'm doing this so I'm moving down by the number of cells stored in the value of the cell Sheet2!\$A\$2. All good and works perfectly.

Now I basically I am copying this formula for other rows etc

=OFFSET(Sheet1\$A\$1,Sheet2!\$A\$2,0)
=OFFSET(Sheet1\$A\$1,Sheet2!\$A\$3,0)
=OFFSET(Sheet1\$A\$1,Sheet2!\$A\$4,0)

As you can see i'm wanting the value in Sheet2!\$A\$X to increase by a value. Now its not necessary by 1. I want this to increase by a value set in another cell. So it references the Sheet2!\$A\$<cell> Is this possible?

So just said I have value of 1 in cell A2

I want my formula's to be;

=OFFSET(Sheet1\$A\$1,Sheet2!\$A\$1,0)
=OFFSET(Sheet1\$A\$1,Sheet2!\$A\$1,0)
=OFFSET(Sheet1\$A\$1,Sheet2!\$A\$1,0)

So it still refers to the Sheet2!\$A\$1 or say Sheet2!A\$2 if the value in A2 = 2, or Sheet2!A\$3 if the value in A2 = 3.  Register To Reply

2. ## Re: Formula referencing a cell using a number of another cell

=INDEX(Sheet1\$A:\$A,INDEX(Sheet2!\$A:\$A,A2))
oughta do it  Register To Reply

3. ## Re: Formula referencing a cell using a number of another cell

Sorry can't get that to work. Simpler example:

In sheet1 I have the following values in the following cells;

A1 = 1
A2 = 3
A3 = 5

In sheet 1 row E within the same sheet I have;
E1 = 5
E3 = 8
E5 = 4

In B1 I want to return the value from Row E based on the value from row A.

In B1;

=E<value in A1>

So in this case it would return the value from cell E1 = 5

If formula was =E<value in A2> A2 = 3, so I would want it to look up cell E3 hence return 8.

If formula was =E<value in A3> A3 = 5, so I would want it to lookup cell E5 hence return 4.

I know this is row number type thing. But its just the principle. Is it possible to put a formula to so first letter never changes. In this case always column 'E'. But the cell it is referring to changes based on the value in the other field.

Sorry if this is as clear as mud and your answer is probably right. But I do know what I mean. Maybe I need to take a break. Thanks for trying to help.  Register To Reply

4. ## Re: Formula referencing a cell using a number of another cell

Edit: wrong solution  Register To Reply

5. ## Re: Formula referencing a cell using a number of another cell

I reckon that's just
=INDEX(\$E:\$E,A1)
if they're all on the same sheet  Register To Reply

6. ## Re: Formula referencing a cell using a number of another cell Originally Posted by JosephP I reckon that's just
=INDEX(\$E:\$E,A1)
if they're all on the same sheet
Thanks that exactly what I was looking for - thanks for your help, much appreciated.  Register To Reply

7. ## Re: Formula referencing a cell using a number of another cell

you're welcome  Register To Reply