Structured reference and offset

Jungan

New Member
Joined
Dec 17, 2015
Messages
6
I am using offset and structured refs in a table to create dynamic arrays.

=OFFSET(Table2[[#Headers];[number]];1;0;ROW([@number])-ROW(Table2[[#Headers];[number]]);1)

My intention with this formula is to create an array that grows in height for each row. But the return value is just a single value.

ROW([@number])-ROW(Table2[[#Headers];[number]])

this counter works fine on its own in a separate column in the table. It also retunrs the right value when nested which I verify with F9. But still the offset returns a single value array for all rows in the table?!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to MrExcel.

To return an array the formula must be confirmed with Ctrl+Shift+Enter. To sum the values for example:

=SUM(OFFSET(Table2[[#Headers],[number]],1,0,ROW([@number])-ROW(Table2[[#Headers],[number]]),1))
 
Last edited:

Jungan

New Member
Joined
Dec 17, 2015
Messages
6
Thanks for your reply Andrew. I am sorry but I understand I didn't use the correct terminology. I am not looking for an array {} I merly want to create a vector(?) with offset. So no arrayformula is needed.
With offset I want to define a vector. I use the column header as reference, hard code the offset , and then I want to use the ROW([@number])-ROW(Table2[[#Headers],[number]]) to make the vector I define with offset one cell longer for each row. But it doesn't work. Offset just returns a single cell vector. Which is odd, because this expression does return a nice counter ROW([@number])-ROW(Table2[[#Headers],[number]]).
If I put this formula
ROW([@number])-ROW(Table2[[#Headers],[number]]) in a separate column and refer to that in the offset formula, I do get a vector that grows for each row.

I want offset to return this:
{10;20;30;40;50;60} but I only get a one value vector.
When I mark the counter formula in the offset it looks like it is counting up. But offset seams to ignore that input.

=OFFSET(Table2[[#Headers];[number]];1;0;{3};1) = 30
=OFFSET(Table2[[#Headers];[number]];1;0;ROW([@number])-ROW(Table2[[#Headers];[number]]);1) = 30 on the third row
 
Last edited:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
OFFSET returns a single value unless it is array-entered. What do you want to do with the result(s)?
 

Jungan

New Member
Joined
Dec 17, 2015
Messages
6
Lets say I want to sum a running total in a column I could use this =SUM($R$1:R1) and just drag the formula downwards. I want to achieve something similar with offset.
=SUM(OFFSET($R$1;0;0;ROWS($R$1:R1);1)) this formula would give the same result and the range defined by offset would grow in length for each row.
Now I want to get the same result by using structured refernces as I have my data in a table.
ROWS($R$1:R1)== ROW(R2)-ROW($R$1)== ROW([@number])-ROW(Table2[[#Headers];[number]]) This works fine, but when I put the structured reference in offset as a counter, offset cannot handle the result, why?
I am not really looking for a workaround, this is rather excel formula exploration :)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
For summing this works for me:

=SUM(OFFSET(Table2[[#Headers],[number]],1,0,ROW([@number])-ROW(Table2[[#Headers],[number]]),1))

as I said in Post #2.
 

Jungan

New Member
Joined
Dec 17, 2015
Messages
6
Ah, you are right, it does work when I use sum. What confused me was that if you mark the offset formula in this expression, regardless of the row and press F9 excel displays just a single value {3}. I was expecting something like this {1;2;3}.
=SUM(OFFSET(Table2[[#Headers],[number]],1,0,ROW([@number])-ROW(Table2[[#Headers],[number]]),1))

Ok so I had another goal with this but it doesn't work
=SUMPRODUCT(OFFSET(Table2[[#Headers];[number]];1;0;ROW([@number])-ROW(Table2[[#Headers];[number]]);1);OFFSET(Table2[[#Headers];[mult]];1;0;ROW([@mult])-ROW(Table2[[#Headers];[mult]]);1))
when this does
=SUM(OFFSET(Table2[[#Headers];[number]];1;0;ROW([@number])-ROW(Table2[[#Headers];[number]]);1);OFFSET(Table2[[#Headers];[mult]];1;0;ROW([@mult])-ROW(Table2[[#Headers];[mult]]);1))
and this
=SUMPRODUCT(OFFSET($F$1;1;0;ROWS($K$1:K1);1);OFFSET($F$1;1;0;ROWS($K$1:K1);1))

I shoul add that all these formulas are used inside table objects.

BR
Erik
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Does this work for you?

=SUMPRODUCT(OFFSET(Table2[[#Headers],[number]],1,0,ROWS(Table2[[#Headers],[number]]:[@number])-1,1),OFFSET(Table2[[#Headers],[mult]],1,0,ROWS(Table2[[#Headers],[mult]]:[@mult])-1,1))
 

Jungan

New Member
Joined
Dec 17, 2015
Messages
6
It does :) I don't know why I overlooked the alternative of using ROWS instead. But there sure is something strange about the excel behaviour here.
In any case thanks alot for helping out Andrew!
 

Forum statistics

Threads
1,082,259
Messages
5,364,100
Members
400,779
Latest member
lumers

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top