Serial Number with like Subtotal function

[vijay.vizzu]

Good Morning All,


Today when i came to my office, i face one problem which my boss asked, i don't have any answer and simply i said let me try it. The problem is that can we put any formula in our data to create s.no. like 1,2,3....etc whenever we put data filter. I mean to say that, we can use SUBTOTAL Function to SUM,COUNT,AVERAGE(1-9)...., but at the same time can we show the s.no. of filtered cells. It should change automatically whenever we change the data filter. For example, assume that i have data in A1:E30, i have auto filtered on E Column and select some criteria. It just filtered, now i wish to put S.no. in A column like 1,2,3....etc. whenever i selected another criteria in my data through auto filter function, then the s.no. in A column should change in the filtered cells.


I hope you understand my problem.


Thanks

Vijay

 

[Hui]

Vijay


If you want the Serial Numbers to change you could use a formula like

A3:

=Max($A$2:A2)+1

Then copy that down


Otherwise could you please upload a sample file with a description of what you require.

 

[vijay.vizzu]

Dear Hui,


Please find below URL


http://sdrv.ms/KeuklB

 

[delvillardennis]

Try this formula:


=MAX(SUBTOTAL(3,D$2:D2))

 

[vijay.vizzu]

Thank you delvillardennis,


It works fine, but can you tell me the logic to use MAX function before SUBTOTAL


Thanks

Vijay