How to reference a header in a structured table without an error?

@Settled and @CQNO are already structured references. But you can simply add the table name by enclosing them as in your sample:

= YourTableName[@Settled] + YourTableName[@CQNO]

Select allOpen in new window

Thanks Stefan but that's not the question.
This is about how to make the non structured references in to structured refs, and these are already inside the table so naming the table inside it self won't help.
Specifically C1 and B1

Your question is still unclear with your unmarked edits. Maybe you should start over to explain what you're trying to do.

The ROW-1 problem in a table requires the OFFSET() function:

=OFFSET([@columnName],-1,0)

Select allOpen in new window

IN my opinion it is perfectly clear.
This is NOT about the row minus one problem in a table.
As I say in the question, it is about row 1 references.
I find INDEX better than OFFSET and it is also not volatile, which OFFSET is.
I am finding your approach critical. Please try to remain positive; my "unmarked edits" are clarifications and do not require to be marked as edits.

Honestly, your question is not clear. B1 and C1 are relative references. Do you mean absolute references like B$1 or $B$1?

I added a column called Indice to the table for the index number. I then used it in your formula as:

=ROW()-ROW(Table1[[#Headers]])   'Formula for Indice
 
=[@Settled]+N(INDEX(Table1[[#All],[Settled]],[@Indice]))*EXACT([@CQNO],@INDEX(Table1[[#All],[CQNO]],[@Indice]))  'Formula for Cumulative

Select allOpen in new window

Structured Reference to row above.xlsx

Note that the formula agrees with the first three Cumulative values in your table, but not the last three.

Yes I was thinking a "helper" column would make the problem go away, but we still end up with an egregiously long formula as compared to the original:

=[@Settled]+(N(B1)*EXACT(C2,C1))

I discovered (this evening) this method of structured referencing:

=Table1[Column2] 3:3.          (EDIT to note omitting the space before the first 3 causes a fail)

which I haven't quite worked out yet. 1:1 gets #NULL! and 1:2 hits row 1 of the table data and so on. So in my scenario we would be looking at for example

=CRefs[Settled] 3:3.    (my structured table is named CRefs.)

So, that "battleship" reference of 3:3 "hits" cell B3 in my example image in my op. 

I cannot help but wonder if this simple method may offer simplicity of a solution and for sure it is "proper" Structured Table methodology which our (frankly ingenious) work-arounds are not.

(I need a little time to take in byundt's formula, for which thanks.)

Anthony

Honestly, your question is not clear. B1 and C1 are relative references. Do you mean absolute references like B$1 or $B$1?

• What can I tell you stefan? Thanks for hanging in there.
• If I meant these to be absolute references, they would be, I am no novice at this, but I am exploring.
• I mean exactly what is shown in my opening post.

I am at a loss how to explain further to you and will reflect on the matter.


Anthony

ok so that works except the last entry is wrong, same problem type as table row one, this is table last row - not sure this constraint was officially in my question though, but we can see below that last row cumulative doesn't work

=[@Settled]+N(INDEX(CRefs[[#All],[Settled]],[@INDICE]))*EXACT([@CQNO],@INDEX(CRefs[[#All],[CQNO]],[@INDICE]))

Select allOpen in new window

All that said, I am looking here for a solution without a helper column, and indeed without a very long formula, since both these types of solution seem to me to defeat the objective of having a structured table in the first place.

I've been messing with LAMBDA and LET as well, but again having to use those looks to me much like using a helper column.

Anthony
 

HERE'S THE ACTUAL FILE EXTRACT
CREFS-EG-EE.xlsx

Anthony,
Your new formula is using implicit intersection to work. CRefs[Settled] 3:3 returns the intersection of the Settled column (without header) and row 3. So CRefs[Settled] 1:1 returns an error value because there is no intersection. And =CRefs[Settled] 2:3 would return two cells.

Frankly, I don't see the benefit of using the implicit intersection over a direct cell reference. You still have the potential for breaking the formulas when you insert or delete rows. A pure structural reference avoids breaking the formula.

Breaking my suggested formula apart, I create a reference to the column of cells including header label using Table1[[#All],[Settled]]. The formula then uses INDEX function and the value of Indice to return a reference to a particular cell (from the row above).

I tried unsuccessfully to remove the references to Table1 from the formulas.

Brad

Hi Brad,

Ok, I've not seen that method before this evening. It just looks nice and simple (not that I ever thought R1C1 multiplan notation was simple) , but as you say if it's not a properly structured method it defeats my objective; which in this case is simply (haha!!) to have 100% structured references in use, without helper columns or long formulas. If I return to the real world for a moment, so leaving perfection aside, this table won't be having insertions and deletions, so B1 and C1 can be allowed to survive. But I'd much rather they did not.

Of course it's the conditional that makes it hard, whereas an unconditional cumulative is easy by comparison.
So far I have been trying to solve it by not including the conditional test if it is row one of the table, because by definition there will be no cumulative from the previous row. So then I end up with too many ifs and buts.

Anthony

In your actual workbook, shouldn't your formula be:

=[@Settled]+N(INDEX(CRefs[[#All],[Cumulative]],[@INDICE]))*EXACT([@CQNO],@INDEX(CRefs[[#All],[CQNO]],[@INDICE]))

Select allOpen in new window

The revised formula references [Cumulative] rather than [Settled] inside the N function

is this useful?

=COUNT(CRefs[Settled] 1:2)  =      1

when entered in table row one and the table replicates it, only the first entry results in 1, all the rest give 2. This differentiates row 1 of the table

In your actual workbook, shouldn't your formula be:

YES

Select allOpen in new window

What's the All doing in here?

(CRefs[[#All],[Cumulative]]

Select allOpen in new window

[#All] gives you the entire column, including header label.

BTW, I prefer the SUMIF.

never thought about sumif. 

BTW, I prefer the SUMIF.

• 
  

me too!

including header label.

lesson learned, thanks

does it insist on using the table name because the headers are outside the "official" table range?

That's a plausible explanation for why you have to use the Table name in the formula. I don't know the official answer. But when I try to delete the Table name, Excel accepts my entry then stubbornly puts it right back in.

yes same here

So reflecting about what I have learned in here:

When working around Structured Table headings the [#Headers] field name doesn't work without also including the Table name. That explains why all my earlier attempts with that field name failed - regardless of whether those attempts would have worked or not. Likewise to include these headings when referencing the table I must use the [#All] field as well. In both cases it appears this may be because the headers are not part of the data area of the table, i.e. that range which is defined by the table name.

and finally: don't forget SUMIF !

and thank you :-)

Anthony

You are building the formula using your keyboard. I'm building it primarily with my mouse. As a result, Excel makes the correct range references.

INDEX is always a welcome visitor edit: works nicely

yes must use mouse more - have done a bit, but not this time.

Strikes me I am not adjusting very quickly at all to this row intersects with column concept; as seen by me extensively in using SUMPRODUCT. It feels very different to my traditional Excel thinking, although it looks like Multiplan methods of years ago (which was I think later morphed in to Excel).

byundt Solutions from above: (so I can find them)

=SUMIF(CRefs[[#Headers],[CQNO]]:[@CQNO],[@CQNO],CRefs[[#Headers],[Settled]]:[@Settled])

Select allOpen in new window

=SUMIF(INDEX([CQNO],1):[@CQNO],[@CQNO],INDEX([Settled],1):[@Settled])

Select allOpen in new window

Having slept on it.

Interesting. I think my entire spreadsheet life, so that's several decades,  I have created running balances (i.e. cumulatives) by  adding "the cell above in this column" to "the cell to my left", using N() for the very first entry because the header text causes a fail. When N() did not exist I had to make the header a zero.

Now we have a fundamental change illustrated above. No longer is "the one above" involved at all. Given that for me "the one above" is the absolute core of a running total this is quite a shock to my long established thinking system. This "new" way of being is.. what is it?

First of all, do not reference the headers at all.
Instead of just adding the above cell to the cell next door, we sum the "entire column to date from the beginning", every time.
Of course this is the same as "add the one above to the one next door" all the way down the column.
Ah. I just realised where my "shock" is coming from. When I started out there were no computers and literally in any column of numbers, on paper written in pencil, I quite literally added the one above to the newest entry. I never re-added the whole column just to get the latest balance. Of course I also never added in the column heading. To this day my spreadsheeting tends to be in the manner of pencil and paper and mental arithmetic (I do NOT miss that). Comp operators ("Comptometer") did amazing things, but they were only available for large pieces of work.
I think Microsoft would benefit from the odd dynosaur in support of their designers so as to align existing humanity's ancient thought processes with the artificial intelligence of Excel. Then we might need significantly less of the extremely complex formulae designed to achieve what humans see as the simplest of operations. Aside from that the docs could offer some depth that is presently not present. Nevertheless, always an engaging world of numbers and the more amazing since dynamic arrays, LAMDA and LET arrived.

So, maybe now I will remember this discovered way of thought.

Anthony


 follow on question:
https://www.experts-exchange.com/questions/29214545/LAMDA-inside-Structured-Table-syntax-to-pass-variables.html

It's a pity that OFFSET(cel_reference,-1,0) is volatile. If it weren't, you would continue with your old way of calculating cumulative sums. It is after all a more efficient process than summing the entire column up to that point.

There is an Excel UserVoice thread suggesting a BEFORE function which would do the needful. You may wish to vote for it. 20 votes puts on the radar screen of an Excel Program Manager. After that, the more votes the higher the priority. 

voted for.