Filter the Absolute Row Number

Mason Rey

Hello, I have an odd request. Is there a way to have a filter so that it always shows the first 25 rows of a sheet and hides everything else below that number. I'm talking about the absolute row number. It needs to be a filter.

We have essentially a list of tasks that are sorted by a number of criteria. I want my team to do the top 25 items on the list every time. So as items complete, they are backfilled with another item. That way they are always looking at 25 items on their to-do list. Thanks

Adam Tyner _Sanofi_

Hi Mason,

You can do this with the use of a couple 'helper' columns. First create an auto-number column. Let's call that column "Row ID". Second, create a "Row Number" column and use this formula in it as a column formula:

=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

This formula will return the relative position of the value in the "Row ID", which means the first row will aways be 1, the second row will always be 2, etc. If you resort, the numbers will persist so that the first row is 1, the second row is 2, ....

Now you should be ready to create a filter for your "Row Number" column and set it to less than or equal to 25.

Best,

Adam

Julie@WD

FYI, I tried this for my own need & it worked like a blessed charm! I've been trying to figure this out for I can't tell you how long! THANK YOU ADAM!!

Aaron Liss

This is amazing! Just note that if you have any reports or formulas based on the modified dates this series of formulas will change the modified date whenever any row is updated. I can't believe this isn't a built in feature!

Dale Murphy

@Adam Tyner _Sanofi_

Have to give you props for this solution. I deployed it in a plan where senior sponsors were insisting on seeing row numbers. Works like a charm.

I haven't seen anything easier or anything that uses fewer columns and formulae.

Thanks.

dm