value of first visible cell

E

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
 
E

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

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.
 
Top