how to use a named range in the Offset function

[JenniferMurphy]

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.

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="align: center"]R/C
[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="colspan: 2, align: center"]Difference[/TD]
[TD="colspan: 2, align: center"]Formulas[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]Shares[/TD]
[TD="align: center"]Balance[/TD]
[TD="align: center"]Literal[/TD]
[TD="align: center"]Relative[/TD]
[TD="align: center"]Literal[/TD]
[TD="align: center"]Relative[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]1/31/19[/TD]
[TD="align: center"]$45.49
[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]$4,549.00[/TD]
[TD="align: center"]$161.00[/TD]
[TD="align: center"]#REF![/TD]
[TD]G6: =F6-OFFSET(F6,1,0)
[/TD]
[TD]H6: =Balance-OFFSET(Balance,1,0)[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]2/28/19[/TD]
[TD="align: center"]$43.88[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]$4,388.00[/TD]
[TD="align: center"]($819.00)[/TD]
[TD="align: center"]#REF![/TD]
[TD]G7: =F7-OFFSET(F7,1,0)[/TD]
[TD]H7: =Balance-OFFSET(Balance,1,0)[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]3/31/19[/TD]
[TD="align: center"]$52.07[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]$5,207.00[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
</tbody>[/TABLE]

Is there a syntax that will make this work?

Thanks

 

[xenou]

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]

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]

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]

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]

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]

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).

 

[xenou]

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)

 

[JenniferMurphy]

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]

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