Generate automatic index predicting use auto-filter

[Luthius]

I have a spreadsheet, and I would like to place an index for each record, ie, every time you fill some data in my column B, an index will be place at same row but in column A.
Assuming that the titles are in row 1, and other information from the line 2, with the formula:
= IF(ISBLANK (B2 ),"", ROW () -1)
If you have nothing on the row filled in column B, no index will be generated, otherwise, I use the row itself (ROW () -1) to generate the index. The -1 is because I'm starting in row 2 ...
Well that serves me well, however, like to have these indexes when using auto-filter.
This information is only for reporting, which makes viewing the contents clearer information analysis, and the formula I posted, does not help in case of auto-filter.
Has anyone experienced a similar situation?

Thanxs

 

[Jerry Sullivan]

You could achieve this with the SUBTOTAL function using COUNTA.
In the example below I've filtered out "Hide" in Col C which is on Rows 3,5,7

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 99px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 38px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Visible Row Index</TD><TD>HeaderB</TD><TD>HeaderC</TD><TD>HeaderD</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Value</TD><TD>Show</TD><TD>Value</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">2</TD><TD>Value</TD><TD>Show</TD><TD>Value</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">3</TD><TD>Value</TD><TD>Show</TD><TD>Value</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">4</TD><TD>Value</TD><TD>Show</TD><TD>Value</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A2</TD><TD>=IF(ISBLANK($B2),"",SUBTOTAL(103,$B$1:$B1))</TD></TR><TR><TD>A9</TD><TD>=IF(ISBLANK($B9),"",SUBTOTAL(103,$B$1:$B8))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Excel tables to the web - Excel Jeanie Html 4

 

[Luthius]

Thanks, post solved with your answer.