Anthony Mellor

asked on

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

Please note all other references are already structured.

In this image see the B1 and C1 references in this structured table?

How do I use structured references to B1 and C1?

This is a problem because B1 and C1 are in the Headers row and not inside the table itself.

I have seen this:

whereas I need the comparison to be only to "the row above" - like ROW()-1 type of thing. Or of course to avoid the comparison if it is outside the table range.

In this image see the B1 and C1 references in this structured table?

How do I use structured references to B1 and C1?

This is a problem because B1 and C1 are in the Headers row and not inside the table itself.

I have seen this:

`TableName[[#Headers],[ColumnHeaderName]]TableName[[#Headers],[ColumnHeaderName]]`

but not managed to make that work yet.When I apply that idea it then replicates in to the whole column and the results are wrong, see here:whereas I need the comparison to be only to "the row above" - like ROW()-1 type of thing. Or of course to avoid the comparison if it is outside the table range.

ASKER

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

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:

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

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

ASKER

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.

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:

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

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

Structured Reference to row above.xlsxNote that the formula agrees with the first three Cumulative values in your table, but not the last three.

ASKER

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

=[@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

ASKER

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.

Anthony

ASKER

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

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

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

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

ASKER

HERE'S THE ACTUAL FILE EXTRACT

CREFS-EG-EE.xlsx

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

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

ASKER

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

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]))`

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

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

=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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

`YES`

ASKER

What's the All doing in here?

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

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

BTW, I prefer the SUMIF.

BTW, I prefer the SUMIF.

ASKER

never thought about sumif.

ASKER

BTW, I prefer the SUMIF.

ASKER

including header label.lesson learned, thanks

ASKER

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.

ASKER

yes same here

ASKER

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

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

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

ASKER

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

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

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

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

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.

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.

ASKER

voted for.

Open in new window