Query : Filtered column, return first value into other cell

[Nouman381]

Hi.
I would like to ask my first question today.

"Column C" has multiple product names, many are repetitive, i applied filter on this column, to get the desired few products only.
So now in return i get 5 products

My need is that whatever returns in this filtered column , i only need only the top cell value of this filtered list, i want this first value to another sheet.

Would appreciate if i get an early kind response. Many thanks.

 

[Nouman381]

when i select different filter products and i get another filtered products list, then again i want to see only the first cell value of the column to return in other sheet.

 

[Nouman381]

Here are the images to explain my query, I am not able to return the desired result

 

[Nouman381]

=INDEX(Table1[Name],MIN(IF(SUBTOTAL(3,OFFSET(C4,ROW(Table1[Name])-ROW(C4),0)),ROW(Table1[Name])-ROW(C4)+1)))

Solved.

 

[Peter_SSs]

Edit: Ah, I was still composing my message when you posted. Similar formula.

Try this (check you table name)

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Nouman381 1.xlsm
	A	B	C	D	E	F	G
1							Samsung
2	Name	Location	Products	Sale
4			Samsung
5			Huawei
7			Samsung
8
Sheet1

Cell Formulas
Range	Formula
G1	G1	=INDEX(Table1[Products],MATCH(1,SUBTOTAL(3,OFFSET(Table1[[#Headers],[Products]],ROW(Table1[Products])-ROW(Table1[[#Headers],[Products]]),,1)),0))