# 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.

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

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

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.

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

Edit: wrong solution

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

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.

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

you're welcome

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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