Static table row numbering regardless of sort/filter

Joachim Martin July 31, 2023

I'd like to show 1-N on my table regardless of sorting or filtering.  I just want a visual indicator so that in meetings people can refer to "row 5" in the current view.

 

With Table Filter, it is Dynamic in that it recalculates after filtering, but not after sorting.

 

Any way to do this?

 

More info:

 

I'm looking for something like the =SUBTOTAL(3,B2:$B$2) rownumbering trick in Excel. 

Basically regardless of sort or filter, I want the first row of the table to be numbered 1, second 2, etc.

 

1 answer

1 vote
Sayed Bares [ServiceRocket]
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 1, 2023

@Joachim Martin I would suggest adding a new column to add the number for your dataset manually so this way it will always be static even if it is sorted or filtered.

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 1, 2023

Hi @Joachim Martin ,

You may use standard Confluence numbered column option for your table and leave the numbering row type field inside the Table Filter macro blank.

Then if you sort or filter your table through the macro, the row numbers will remain as they are.

Like # people like this
Joachim Martin August 1, 2023

I'm looking for something like the =SUBTOTAL(3,B2:$B$2) rownumbering trick in Excel. 

Basically regardless of sort or filter, I want the first row of the table to be numbered 1, second 2, etc.

 

May not be possible but thought I'd ask the experts!

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 1, 2023

Hi @Joachim Martin ,

The Table Spreadsheet macro supports the =SUBTOTAL(3, ...) function.

But it seems that I'm getting the idea wrong. :)

In my A column I have data and in the B column I try to create row numbers:

=SUBTOTAL(3,$A$2:A2)

=SUBTOTAL(3,$A$2:A3)

=SUBTOTAL(3,$A$2:A4)

I get 1, 2 and 3 that is correct.

But when sorting or filtering, the row numbers change and they are not static (both for Excel and Table Spreadsheet).

As I know, the =SUBTOTAL(3, ...) function equals the =COUNTA function that counts not empty cells in the range.

So, when sorting, only "strict" formulas work for me:

=SUBTOTAL(3,$A$2:$A$2)

=SUBTOTAL(3,$A$2:$A$3)

=SUBTOTAL(3,$A$2:$A$4)

What concerns filtering, even "strict" formulas break the numbering. I've found in Excel documentation that =SUBTOTAL(3, ...) function includes and =SUBTOTAL(103, ...) function ignores manually hidden rows. But the "filtered out" rows are always ignored. So, it seems that I get the correct behavior.

So, if you get the case to work, please share with us and future readers of this question.

Joachim Martin August 1, 2023

I am trying to add this row number to a Page Properties Report.  So I don't think the Table Spreadsheet will work.

 

Here's what I'm looking for

 

Initial

2023-08-01_16-39-48.png

 

After filtering I still get 1,2,3

 

2023-08-01_16-43-20.png

 

After sorting I still get 1,2,3

 

2023-08-01_16-43-40.png

 

It is not surprising that this is not possible in Confluence but I was hoping that the Table Transformer could do this.  It would have to recalculate the number value after every sort/filter in the nested table filter macro.

 

This is a super edge case that is probably not worth deep diving into.  In my case, participants in the project planning meetings can just use the name of the projects.  They are just kind of long and similar to each other so I was thinking some numbering could help.  We also have jira #s so we can use that as well.

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 1, 2023

Now I see that I got your case totally wrong - you need your numbers to be dynamic and not static. At first I got your question exactly like @Sayed Bares [ServiceRocket] did. :)

So, the Table Spreadsheet macro then works exactly as you need but as for now it doesn't recognize the Page Properties Report macro - this feature is on our roadmap and we'll get back here after the release and notify you.

What concerns the Table Filter macro, then it also works correct if you choose the dynamic row numbering type and use filtration and sorting within the macro.

If you use native sorting from the table header, then the numbers become static and move within the table as well.

So, you may set the default sorting once inside the Table Filter macro from the page edit mode and then only filter your table from the page view mode without using native Confluence sorting.

Alexey Mikhaylov _Stiltsoft_
Contributor
September 29, 2023

Hello @Joachim Martin  ,

Let me inform you we've released a new macro - Spreadsheet from Table - which allows transferring your regular table or a table outputting a macro's content (such as, for example, Page Properties Report) into Table Spreadsheet with all its tools. This should help your use case.

P.S. If you don't see the macro available in your Confluence Cloud instance, please approach your Confluence admin and ask him to update Table Filter and Charts for Confluence manually from the administration settings.

Like # people like this

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
TAGS
AUG Leaders

Atlassian Community Events