# Excel: can you calculate cell references? **URL:** \ **Category:** Factual Questions\ **Created:** [December 5, 2008, 12:14am UTC](https://boards.straightdope.com/t/excel-can-you-calculate-cell-references/475779 "2008-12-05T00:14:22Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![amarone](https://avatars.discourse-cdn.com/v4/letter/a/e0b2c6/32.png) [@amarone](https://boards.straightdope.com/u/amarone)\ **Post date:** [December 5, 2008, 12:14am UTC](https://boards.straightdope.com/t/excel-can-you-calculate-cell-references/475779/1 "2008-12-05T00:14:22Z")
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? ---
**Author:** ![AllShookDown](https://avatars.discourse-cdn.com/v4/letter/a/d9b06d/32.png) [@AllShookDown](https://boards.straightdope.com/u/AllShookDown)\ **Post date:** [December 5, 2008, 12:38am UTC](https://boards.straightdope.com/t/excel-can-you-calculate-cell-references/475779/2 "2008-12-05T00:38:03Z")
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. ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [December 5, 2008, 12:48am UTC](https://boards.straightdope.com/t/excel-can-you-calculate-cell-references/475779/3 "2008-12-05T00:48:05Z")
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.) ---
**Author:** ![amarone](https://avatars.discourse-cdn.com/v4/letter/a/e0b2c6/32.png) [@amarone](https://boards.straightdope.com/u/amarone)\ **Post date:** [December 5, 2008, 12:50am UTC](https://boards.straightdope.com/t/excel-can-you-calculate-cell-references/475779/4 "2008-12-05T00:50:13Z")
Thank you - that did it. I had to make minor changes: =OFFSET(Sheet2!B30,D1) needed to be =OFFSET(Sheet2!$B$30,D1,0). ---
**Author:** ![amarone](https://avatars.discourse-cdn.com/v4/letter/a/e0b2c6/32.png) [@amarone](https://boards.straightdope.com/u/amarone)\ **Post date:** [December 5, 2008, 12:52am UTC](https://boards.straightdope.com/t/excel-can-you-calculate-cell-references/475779/5 "2008-12-05T00:52:00Z")
> [@KneadToKnow](#): > > 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.) 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. ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [December 5, 2008, 12:54am UTC](https://boards.straightdope.com/t/excel-can-you-calculate-cell-references/475779/6 "2008-12-05T00:54:13Z")
Missed the edit window for a further follow-up: If you do use **AllShookDown** ’s method, I’d recommend against this: > [@](#): > > 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… 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. ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [December 5, 2008, 12:55am UTC](https://boards.straightdope.com/t/excel-can-you-calculate-cell-references/475779/7 "2008-12-05T00:55:21Z")
> [@amarone](#): > > 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. [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. ---
**Author:** ![amarone](https://avatars.discourse-cdn.com/v4/letter/a/e0b2c6/32.png) [@amarone](https://boards.straightdope.com/u/amarone)\ **Post date:** [December 5, 2008, 1:47am UTC](https://boards.straightdope.com/t/excel-can-you-calculate-cell-references/475779/8 "2008-12-05T01:47:05Z")
Thanks again - I have it working now. ---
**Author:** ![Bean\_Counter](https://avatars.discourse-cdn.com/v4/letter/b/db5fbb/32.png) [@Bean\_Counter](https://boards.straightdope.com/u/Bean_Counter)\ **Post date:** [December 5, 2008, 4:23am UTC](https://boards.straightdope.com/t/excel-can-you-calculate-cell-references/475779/9 "2008-12-05T04:23:36Z")
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.