Increase cells by 1 but exclude hidden ones

Clouds

New Member
Joined
Aug 27, 2009
Messages
6
I have a list of overall results. Within that list I have different classes that I filter via an "Auto Filter"

If I currently filter that list, the person that came say 5th overall might have won another class. It currently shows that person's position as number "5", but I would like it to show "1", the second person in that class as "2" etc etc.

Is there a formula that I can put into the cells that will increase by 1, but exclude the hidden cells above it when the classes are filtered.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Sorry Clouds, but (if I understand your question) the answer is no.
Formulas will update (even in filtered rows) whether their columns are hidden or not.

(And, welcome to the board!)
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
been lurking for quiet a while
:LOL:

Well, nice to see you finally come aboard then.
(It's a drag the answer to your first question was 'no'.) :whistle:
 
Last edited:

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Clouds,

Are you sorting the filtered results or using the RANK() function?
 

Clouds

New Member
Joined
Aug 27, 2009
Messages
6
Weaver - I am filtering using an Auto Filter.

I figured it out. I thought that somehow the Subtotal function could help me and spent a while trying each "Function_num". Once I had the formula doing the correct calculation, it was a simple matter of hiding it for the blank cells. I ended up with the formula below in my A column and the C column is the persons name. (This is the formula out of cell A21)

<TABLE style="WIDTH: 415pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=553 x:str><COLGROUP><COL style="WIDTH: 415pt; mso-width-source: userset; mso-width-alt: 20224" width=553><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent; WIDTH: 415pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8 0.5pt; BORDER-RIGHT: #ece9d8 0.5pt" class=xl65 height=17 width=553 x:str="'=IF(C21="","",(SUBTOTAL(3, $C$10:C21)))">=IF(C21="","",(SUBTOTAL(3, $C$10:C21)))



</TD></TR></TBODY></TABLE>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top