any function can get first 3 charecters of all cells in the first visible row
in the filtered data

thanks all of you

Just to add...
when i filter 200 rows data, my first visible row is 25 (rows 5:205)
give me formula at a2,b2,c2.... to get 3chars of a25,b25,c25....
i want to see on row 2, a2 "Uni" a25 "United", b2 "Oil" b25 "Oils & Lubs",
c2 "hard" c25 "hardings"....

so everytime user changes filter row 2 will show 3chars all visible cells in
the 1st row !

thanks !

"Eddy Stan" wrote:

any function can get first 3 charecters of all cells in the first visible row
in the filtered data

thanks all of you

Try this array formula** :

Array entered in A2 and copied across as needed:

=LEFT(INDEX(A5:A205,MATCH(1,SUBTOTAL(3,OFFSET(A5:A 205,,,ROW(A5:A205)-MIN(ROW(A5:A205))+1)),0)),3)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Eddy Stan" wrote in message
...
any function can get first 3 charecters of all cells in the first visible
row
in the filtered data

thanks all of you

Thank you so much and it is timely help.
best wishes 2 you.



"T. Valko" wrote:

Try this array formula** :

Array entered in A2 and copied across as needed:

=LEFT(INDEX(A5:A205,MATCH(1,SUBTOTAL(3,OFFSET(A5:A 205,,,ROW(A5:A205)-MIN(ROW(A5:A205))+1)),0)),3)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Eddy Stan" wrote in message
...
any function can get first 3 charecters of all cells in the first visible
row
in the filtered data

thanks all of you

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Eddy Stan" wrote in message
...
Thank you so much and it is timely help.
best wishes 2 you.



"T. Valko" wrote:

Try this array formula** :

Array entered in A2 and copied across as needed:

=LEFT(INDEX(A5:A205,MATCH(1,SUBTOTAL(3,OFFSET(A5:A 205,,,ROW(A5:A205)-MIN(ROW(A5:A205))+1)),0)),3)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Eddy Stan" wrote in message
...
any function can get first 3 charecters of all cells in the first
visible
row
in the filtered data

thanks all of you