# How can I refer to a cell that is a constant number of cells away from a particular cell?

I have two identical tables on a sheet: one that contains text for reference, and a second that contains values.

I am trying to sum up some cells in the second table but using the addresses of cells in the first table to do so.

For example, if this is my data:

``````B1 contains apple  B11 contains 0.5
B5 contains egg    B15 contains 1.5
C7 contains ham    C17 contains 0.5
``````

is there a way I can write a formula `=SUM(B1,B5,C7)` but increment the row number of the arguments by 10 using some function?

-
In the example you have provided it seems like you're trying to add text cells, is that right ? –  Darius Apr 3 '13 at 23:04
No I want the sum of the numbers –  Shekhar Apr 4 '13 at 0:11

You can use Offset() like this:

``````=SUM(OFFSET(B1,10,0),OFFSET(B5,10,0),OFFSET(C7,10,0))
``````

Mind you that Offset() is volatile and the formula will recalculate any time ANY cell is changed in the workbook. It's best to avoid too many Offset() functions because they can bring down performance on large workbooks.

-

Copy `=SUM(B1,B5,C7)` and paste that ten rows lower down.

I don't seem to understand the comment:

On the right is a possible alternative. Copy the numbers to a separate sheet (placed to match text in existing sheet), group the two sheets and simply stick with existing formula/location.

-
that would work if the cells 10 rows above are empty –  Shekhar Apr 4 '13 at 15:13
I was guessing you wanted a total (cost/number of calories?) from simply picking 'ingredients' say using AutoSum but do not understand your comment - are you copying down over ten rows (I meant only copy into the tenth [one cell])? –  pnuts Apr 4 '13 at 15:40
I meant the cell where you write the original formula (from where you copy and paste the formula 10 rows below) , if that already contains come content you will not be able to write a formula there –  Shekhar Apr 4 '13 at 17:15
? I never specified where that should be (you did not either) and relied on you not to choose a cell that was already occupied! [Still makes NO sense to me :-).] –  pnuts Apr 4 '13 at 18:36