Get first filtered cell in a row

[Cowichandave]

Is it possible with a filtered table to get a specific cell say b10 from the first row and have it display elsewhere and if the filter changes the cell would change with it.

 

[StephenCrump]

Yes, but you'd need to run some VBA to make it work. Is VBA an option for you?

 

[Cowichandave]

Yes, but you'd need to run some VBA to make it work. Is VBA an option for you?

No problem with vba

 

[StephenCrump]

Here's the basic idea ...

	A	B
1	Name	Value
2	A	2
3	B	3
4	C	5
5	D	7
6	E	11
7	F	13
8	G	17
Sheet1

	A	B	C	D
1
2		First value	2
3
4		Subtotal	58
5
Sheet2

Cell Formulas
Range	Formula
C4	C4	=SUBTOTAL(9,FilteredValues)

Named Ranges
Name	Refers To	Cells
FilteredValues	=Sheet1!$B$2:$B$8	C4

FirstValue:=Sheet2!$C$2

'In Sheet2 module
Private Sub Worksheet_Calculate()

    Range("FirstValue") = Sheet1.Range("FilteredValues").SpecialCells(xlCellTypeVisible)(1).Value
  
End Sub

To detect the change in filter in Sheet1, I have put a SUBTOTAL() formula in Sheet2 and used the Worksheet_Calculate event. You could put this somewhere else (quiet) in the workbook to minimise calls to Worksheet_Calculate.

-------->

 

[Dave Patton]

Formula to show first row of Table

First result is with filter Select All
Second result with filter Select C

T202312a.xlsm
	A	B
1
2	First	2
3
4	Name	Value
5	A	2
6	B	3
7	A	5
8	A	7
9	C	11
10	C	13
11	C	17
12
3b

Cell Formulas
Range	Formula
B2	B2	=LET(c,B5:B9999,TAKE(FILTER(c,BYROW(c,LAMBDA(br,SUBTOTAL(3,br)))=1),1))

Filter or First Row of unfiltered

T202312a.xlsm
	A	B
2	C	11
3
4	Name	Value
9	C	11
10	C	13
11	C	17
3b

Cell Formulas
Range	Formula
A2:B2	A2	=LET(c,A5:A9999,TAKE(FILTER(c,BYROW(c,LAMBDA(br,SUBTOTAL(3,br)))=1),1))

 

[Fluff]

Another formula solution for xl 2016 using a helper column.

Fluff.xlsm
	A	B	C	D
1
2		Forest of Dean
3
4	County	District	Ward	Is visible
13	Gloucestershire	Forest of Dean	Cinderford East	1
14	Dorset	West Dorset	Maiden Newton	1
20	Greater Manchester	Tameside	Hyde Werneth	1
25	Cornwall	Cornwall	Roche	1
26	West Yorkshire	Kirklees	Holme Valley North	1
Sheet5

Cell Formulas
Range	Formula
B2	B2	=INDEX(B5:B100,MATCH(1,D5:D100,0))
D13:D14,D20,D25:D26	D13	=SUBTOTAL(103,A13)

 

[Peter_SSs]

Another formula solution for xl 2016 without using a helper column.

23 12 11.xlsm
	A	B	C
1
2		Forest of Dean
3
4	County	District	Ward
13	Gloucestershire	Forest of Dean	Cinderford East
14	Dorset	West Dorset	Maiden Newton
20	Greater Manchester	Tameside	Hyde Werneth
25	Cornwall	Cornwall	Roche
26	West Yorkshire	Kirklees	Holme Valley North
27
Sheet2 (2)

Cell Formulas
Range	Formula
B2	B2	=INDEX(B5:B100,MATCH(1,SUBTOTAL(3,OFFSET(B5,ROW(B5:B100)-ROW(B5),,1)),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[StephenCrump]

Another formula solution for xl 2016 without using a helper column.

Ha ha, the old SubTotal/Offset approach works again.

Yes, but you'd need to run some VBA to make it work.

OK, I retract my statement. To quote a former Prime Minister (Australian, not Boris), it's dead, buried and cremated.