# MS Excel question - refer to the previous cell not by name

**URL:** <https://boards.straightdope.com/t/ms-excel-question-refer-to-the-previous-cell-not-by-name/438848>\
**Category:** Factual Questions\
**Created:** [February 23, 2008, 2:32pm UTC](https://boards.straightdope.com/t/ms-excel-question-refer-to-the-previous-cell-not-by-name/438848 "2008-02-23T14:32:17Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\
**Post date:** [February 23, 2008, 2:32pm UTC](https://boards.straightdope.com/t/ms-excel-question-refer-to-the-previous-cell-not-by-name/438848/1 "2008-02-23T14:32:17Z")

</div>

Is it possible to refer to a cell above or bellow the one containing a sum without refering to it’s name?

Say I am in cell b4 and I want to do a sum using what’s in b3 how can I refer to it without specifying ‘b3’.

The reason I ask is because if I delete an entire row (b3) then excel gives the #ref! error.

I want it to keep the reference and use the **new contents of b3 after the row delete**

---

<div class="post-metadata">

**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\
**Post date:** [February 23, 2008, 2:41pm UTC](https://boards.straightdope.com/t/ms-excel-question-refer-to-the-previous-cell-not-by-name/438848/2 "2008-02-23T14:41:08Z")

</div>

You could make it an absolute reference rather than a relative on using dollar signs, The form for that is something like $B$2+$B$3

---

<div class="post-metadata">

**Author:** ![iwakura43](https://avatars.discourse-cdn.com/v4/letter/i/9de0a6/32.png) [@iwakura43](https://boards.straightdope.com/u/iwakura43)\
**Post date:** [February 23, 2008, 2:45pm UTC](https://boards.straightdope.com/t/ms-excel-question-refer-to-the-previous-cell-not-by-name/438848/3 "2008-02-23T14:45:34Z")

</div>

=OFFSET(x,y) will give you the contents of the cells x rows and y columns away (negative numbers go left / up, positive numbers go right / down.

---

<div class="post-metadata">

**Author:** ![iwakura43](https://avatars.discourse-cdn.com/v4/letter/i/9de0a6/32.png) [@iwakura43](https://boards.straightdope.com/u/iwakura43)\
**Post date:** [February 23, 2008, 2:51pm UTC](https://boards.straightdope.com/t/ms-excel-question-refer-to-the-previous-cell-not-by-name/438848/4 "2008-02-23T14:51:10Z")

</div>

Something else that might work for you–I don’t fully understand what you need–is INDIRECT.

=INDIRECT(“B3”)

will always give you a reference to the current B3, no matter what you do with additions and deletions. The argument to INDIRECT is text specifying the address of the cell you want. For instance, if you have

B2 1  
B3 2  
B4 5  
B5 7

then =SUM(INDIRECT(“B2”):INDIRECT(“B5”)) gets you 15

---

<div class="post-metadata">

**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\
**Post date:** [February 23, 2008, 2:55pm UTC](https://boards.straightdope.com/t/ms-excel-question-refer-to-the-previous-cell-not-by-name/438848/5 "2008-02-23T14:55:17Z")

</div>

I’ll try the offset.

While waiting for answers I was trying to use the r1c1 type of referencing (so that I could do R[0]C[-2] but it wouldn’t let me!

---

<div class="post-metadata">

**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\
**Post date:** [February 23, 2008, 3:03pm UTC](https://boards.straightdope.com/t/ms-excel-question-refer-to-the-previous-cell-not-by-name/438848/6 "2008-02-23T15:03:08Z")

</div>

Offset worked , thanks **iwakura43**  
=SUM(OFFSET(D17,-1,0)+(B17-C17))  
It’s a simple financial sheet where I copied a month’s data to the following month, and then remove items that don’t apply.