autofilter result

  • Thread starter Thread starter marcel
  • Start date Start date
M

marcel

If you use autofilter then below you get the results, but you can not
use this result directory as a reference since this row can be any
number the rest is just invisible, how can I use that row number or get
that rownumber (which I can see) further in the program, so how can I
use that selected rownumber(s) which I can see, but not use:
example
3 City = New York (the filter)
10 Newyork info

So how in this example can I propogate that rownumber 10 (how can I get
that number), which I can see, hope it is clear!
Kind regards, Marcel.
 
Note that it will always return 2 if the range is not filtered but it will
work when you apply the filter,
if more than one item it will return row number of the first instance

=MATCH(1,SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$20)-MIN(ROW($A$2:$A$20)),,)),0)+
1

entered with ctrl + shift & enter
 
Beware of the linewrap, the end of the formula is +1

--

Regards,

Peo Sjoblom

Peo Sjoblom said:
Note that it will always return 2 if the range is not filtered but it will
work when you apply the filter,
if more than one item it will return row number of the first instance

=MATCH(1,SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$20)-MIN(ROW($A$2:$A$20)),,)),0)+
1

entered with ctrl + shift & enter
Click to expand...
 
Mr Sjoblom,

I reallly like your help!
I have a problem my excel (xp version) does not understand or may be
error in formula the subtotal,

I have copied straight the formula in the cell, seems to be something
wrong?

Any idea, thanks and regards, Marcel
 
To be confirmed with control+shift+enter, not just with enter...

=CELL("Row",INDEX(A4:A100,MATCH(1,(SUBTOTAL(3,OFFSET(A4:A100,ROW(A4:A100)-MI
N(ROW(A4:A100)),,1))*(A4:A100="NewYork")),0)))

where row 3 houses the labels and A4:A100 is the range of interest (that is,
one of the ranges to which you apply a filter). This formula gives you the
native row number of the first cell that houses a "New York" value.
 
Mr akyuerek,

First of all thanks for the input!

What is this Cntrl shift return story, are you not talking about copy /
paste the data in a cell???
Please explain.

Regarding your method, I see in the formula 'new york' but this was an
example I want to use the autofilter in general, so that I can chose
everything I need

Kind regards, Marcel.
 
Mr Akyuerk,

First of all thanks for the response!
What do you mean with Shift Cntrl Enter, is that not the same as copy /
paste the line to a cell in Excel?? I am confused

Secondly I would like to have a general formula,
I use autofilter and I can filter on many issues, finally I have a few
rows or one row left, and the data of the first row I want to copy or
use that information of that row somewhere else, that can be done by
either exporting one way or the other that row number that shows up or
any other way, but I do not know why, I SEE the result, but I can not
propogate that row further in my excel sheet...
Hope my question is more clear..!
Thanks in advance, kind regards, Marcel Wissing.
 
1 Equipment Technical identification no.
4 10042 CE-41.5254-004
5 10042143 CE-41.5254-019
6 10042148 CE-41.5254-024
 
1 Equipment Technical identification no.
4 10042 CE-41.5254-
5 10042 CE-41.5254-
6 10042 CE-41.5254-

So in this example I would like to have row 4 which is blue with the
autofilter being copied somewhere else to a fixed position so I can use
that chosen data further on in the excel sheet.
Thanks an regards, Marcel.
 
Back
Top