value of first visible cell
 

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

thanks all of you

value of first visible cell
 

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

value of first visible cell
 

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

value of first visible cell
 

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

value of first visible cell
 

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