Is it possible to freeze the far left column in a table so that if another column is filtered or sorted this frozen column doesn’t do anything? I would like the column to have an ascending ranking number (i.e. 1,2,3,4…..) that way if I filter a different column by Item and then sort another column by sales volume the result would be the list of items by sales in the region with a rank number for that situation and not retain the number that orginally associated with the row that contained the item.
Hi Mike,
Welcome to our forum! You have two things going on here, first you have an index number which is displayed in your first 4 tables. It's not a ranking, it simply numbers the rows in the table. This can be hard keyed as it never changes.
Then in the 5th Table you want to see a proper ranking that ignores hidden rows. Assuming you don't have Office 365 and the new Dynamic arrays, you'll want this formula:
=SUM(IF(SUBTOTAL(103,OFFSET($D$3:$D$18,ROW($D$3:$D$18)-ROW($D$3),0,1))>0,IF(D3<$D$3:$D$18,1)))+1
Entered with CTRL+SHIFT+ENTER
If you have dynamic arrays, you might like to try something like this scroll and sort table.
Mynda
