value of first visible cell

[Eddy Stan]

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

thanks all of you

 

[Eddy Stan]

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 !

 

[T. Valko]

Try this array formula** :

Array entered in A2 and copied across as needed:

=LEFT(INDEX(A5:A205,MATCH(1,SUBTOTAL(3,OFFSET(A5:A205,,,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.

 

[Eddy Stan]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!