Excel: can you calculate cell references?

Factual Questions
1

This is for the spreadsheet that I maintain to track the Celebrity Deathpool over in the Games forum.

I have a cell that pulls in the value from a cell in another sheet. Let’s say it is =Sheet2!B30. In the cell below this one I want to have =Sheet2!B47; the next one down will be =Sheet2!B64. In other words, I am stepping 17 rows each time.

Is there any way I can calculate this rather than having to set up the reference every time?

2

I don’t have access to Excel at the moment so can’t check this, and I haven’t used the OFFSET function for a while but I’ll give it a shot.

In a separate column type in the number 17. Let’s say you type that in cell D1. In cell D2, enter the formula =D1+17. Copy that formula down as many rows as you think you’ll need. The results of that should be 17, 34, 51…

In the cell where you want to reference the cell from the other sheet type your normal =Sheet2!B30.

In the cell below that type =OFFSET(Sheet2!B30,D1) Translation: return the value of the cell that’s offset 17 rows down from Sheet2!B30.

Copy that formula down. Someone else will probably chime in with a more elegant way of doing it.

3

You can make it simpler by just using OFFSET(Sheet2!B30,17,0), OFFSET(Sheet2!B30,34,0), etc. (The zero is for columns. My version of Excel requires it. YVMV.)

4

Thank you - that did it. I had to make minor changes:

=OFFSET(Sheet2!B30,D1) needed to be =OFFSET(Sheet2!$B$30,D1,0).

5

I needed the zero for columns, but I am trying to avoid typing in every row - your method looks as if I would need to type each row and manually change 17 to 34, then 51 etc.

6

Missed the edit window for a further follow-up:

If you do use AllShookDown’s method, I’d recommend against this:

Let D1 be 17, then have D2 be =D$1+D1. Then copy that down. This way, if the interval ever changes, all you have to do is change D1 to the new interval and all of the rest will change, too.

7

[del]Once you type two or three, try using AutoFill to copy them down. You might get lucky and have it recognize the pattern.[/del]

Tried it, just got a repeating sequence. I’d go with AllShookDown’s method with my amendment.

8

Thanks again - I have it working now.

9

I don’t like the idea of needing multiple columns to enact this. Therefore:

  1. Start in D1. Enter: =OFFSET(Sheet2!$B$30,(row()-1)*17,0)

  2. Copy down.

  3. Profit.