Generate automatic index predicting use auto-filter

L

Luthius

Active Member
Joined
Apr 5, 2011
Messages
324
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Using Unique, Filter and ChooseCols dynamically
Replies
7
Views
377
Fluff
Fluff
N
  • Question
Change dynamic array to sort by last 4 digits
Replies
3
Views
208
djclements
djclements
serge
  • Question
#N/A cell skip.
Replies
3
Views
204
Peter_SSs
Peter_SSs
N
  • Question
MATCH and INDEX help
Replies
3
Views
718
etaf
etaf
J
  • Question
Using index and match from another source excel file
2
Replies
10
Views
562
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,221,567
Messages
6,160,538
Members
451,655
Latest member
rugubara

Share this page

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
Back
Top