how to use a named range in the Offset function

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,447
Office Version
  1. 365
Platform
  1. Windows
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
CDEFGHIJ
4



DifferenceFormulas
5DatePriceSharesBalanceLiteralRelativeLiteralRelative
61/31/19$45.49
100$4,549.00$161.00#REF!G6: =F6-OFFSET(F6,1,0)
H6: =Balance-OFFSET(Balance,1,0)
72/28/19$43.88100$4,388.00($819.00)#REF!G7: =F7-OFFSET(F7,1,0)H7: =Balance-OFFSET(Balance,1,0)
83/31/19$52.07100$5,207.00




<tbody>
</tbody>

Is there a syntax that will make this work?

Thanks
 

Excel Facts

Links? Where??
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
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jul 23, 2011
Messages
1,447
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 23, 2011
Messages
1,447
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jul 23, 2011
Messages
1,447
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top