# how to use a named range in the Offset function

#### JenniferMurphy

##### Well-known Member
In the table below, I have assigned the name "Balance" to column F. I would like to replace "F6" in I6 with the named range, "Balance", but the offset function doesn't like it.

 R/C C D E F G H I J 4 Difference Formulas 5 Date Price Shares Balance Literal Relative Literal Relative 6 1/31/19 \$45.49 100 \$4,549.00 \$161.00 #REF! G6: =F6-OFFSET(F6,1,0) H6: =Balance-OFFSET(Balance,1,0) 7 2/28/19 \$43.88 100 \$4,388.00 (\$819.00) #REF! G7: =F7-OFFSET(F7,1,0) H7: =Balance-OFFSET(Balance,1,0) 8 3/31/19 \$52.07 100 \$5,207.00

<tbody>
</tbody>

Is there a syntax that will make this work?

Thanks

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### xenou

##### MrExcel MVP
Hi, I don't think offset makes sense if you are offsetting from a range that is an entire column. You could just use regular addressing such as in your 'literal' formula for that case. Or am I missing something about what you are trying to accomplish?

#### JenniferMurphy

##### Well-known Member
Hi, I don't think offset makes sense if you are offsetting from a range that is an entire column. You could just use regular addressing such as in your 'literal' formula for that case. Or am I missing something about what you are trying to accomplish?

I named the range (column) because I want to use named addressing, not literal addressing. I want to use the name "Balance" in some formula that will calculate the difference between the balance in the row where the formula is called and the balance in the cell just below that one.

I want a non-literal version of the formula in G6. (PS: In my original post, I said "I6" when I meant "G6".)

#### xenou

##### MrExcel MVP
A named range that refers to an entire column would not work the same way as a cell address that is a single cell. You have apples and oranges here. Are you expecting the balance column to move around such that you don't know where it is? Why would it be moving around?

#### xenou

##### MrExcel MVP

So this would work if you make balance refer to \$F\$1 instead of \$F\$F:

=OFFSET(Balance,ROW()-1,0)-OFFSET(Balance,ROW(),0)

That is, you offset from a cell (in this case, F1).

But if you think about it a simple formula is an offset. So if your balance column isn't ranging far and wide your "literal" formula says to offset from the current cell one cell to the left and one cell down. Which is all you want.

#### JenniferMurphy

##### Well-known Member
A named range that refers to an entire column would not work the same way as a cell address that is a single cell.
Of course.

You have apples and oranges here.
Not at all.

I assigned the name "Price" to column D and "Shares" to column E. The formulas in F6:F8 are all "=Price*Shares". That formula is able to use the values in the named ranges that are on the same row as the calling cell. The Offset function is too stupid or too anal to do that, so I need a formula that will put the cell address in place of "Balance" in that formula.

Is there a way to extract from a named column the address of the cell that is on the same row as the calling cell?

Are you expecting the balance column to move around such that you don't know where it is? Why would it be moving around?
Cells, columns, rows all move around all the time. But this is not the main point. Formulas with literal cell addresses are difficult to read and error-prone. I try to avoid them at all costs. Excel makes this easy in some situations and nearly impossible in others.

#### xenou

##### MrExcel MVP

See my last post for one solution.

to this question:
Is there a way to extract from a named column the address of the cell that is on the same row as the calling cell?

Possibly but not directly with offset alone - because you are not really wanting to offset from a column but from a cell (strictly speaking an offset from a column would be another column - and there's not enough room in a spreadsheet for two columns that are already the size of the whole spreadsheet!)

Edit: actually, as I think of it Index() is what you really want. Although probably you should not toss out formulas as being of little value since they are the heart and soul of many well-crafted spreadsheets around the globe and probably in space as well (assuming there are spreadsheets on the international space station and some of them are also well-crafted).

Last edited:

#### xenou

##### MrExcel MVP
Here would be the use of INDEX with a range named Balance that is an entire column (such as \$F:\$F):

=INDEX(Balance,ROW())-INDEX(Balance,ROW()+1)

Last edited:

#### JenniferMurphy

##### Well-known Member
So this would work if you make balance refer to \$F\$1 instead of \$F\$F:

=OFFSET(Balance,ROW()-1,0)-OFFSET(Balance,ROW(),0)

That is, you offset from a cell (in this case, F1).
That's an interesting solution. I hadn't thought of that. It would make this formula work, but would cause all my others to fail.

But if you think about it a simple formula is an offset. So if your balance column isn't ranging far and wide your "literal" formula says to offset from the current cell one cell to the left and one cell down.
Yes, but as I already said, this misses the main point. I am trying to avoid literal, absolute cell addressing for a bunch of reasons. Columns moving around is just one and probably not the main one.

Which is all you want.
I think it will work better if you let me tell you what I want. I've tried several times to tell you what that is.

Let's forget my application completely and focus on this question: Is there a formula that will return the address of the cell in a named column that is on the same row as the calling cell?

#### JenniferMurphy

##### Well-known Member
Here would be the use of INDEX with a range named balance that is an entire column:

=INDEX(Balance,ROW())-INDEX(Balance,ROW()+1)

Perfect. Thank you very much

Replies
11
Views
216
Replies
13
Views
2K
Replies
3
Views
579
Replies
2
Views
275
Replies
5
Views
7K

1,129,556
Messages
5,637,027
Members
416,954
Latest member
Gohar hussain

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back