SOLVED

Get the filtered/selected value from a table.

Steel Contributor

Hi,

 

The goal here is to use Slice to select a value and get that value, so I can use it in other formulas.

 

See the attached sheet for example, explanation.

 

Best Regards

- Geir

 

3 Replies
best response confirmed by Geir Hogstad (Steel Contributor)
Solution

@Geir Hogstad 

In Excel two functions take hided content into account, SUBTOTAL() and AGGREGATE(). You may add helper column as here

image.png

in which function 

=AGGREGATE(3,3,[@Kode])

returns 0 or 1 depend on row is filtered or not. To return first filtered value we may use

=INDEX([Kode],MATCH(1,[IsNotFiltered],0))

Result will be like

image.png

Perfect, thank you @Sergei Baklan 

@Geir Hogstad , you are welcome

1 best response

Accepted Solutions
best response confirmed by Geir Hogstad (Steel Contributor)
Solution

@Geir Hogstad 

In Excel two functions take hided content into account, SUBTOTAL() and AGGREGATE(). You may add helper column as here

image.png

in which function 

=AGGREGATE(3,3,[@Kode])

returns 0 or 1 depend on row is filtered or not. To return first filtered value we may use

=INDEX([Kode],MATCH(1,[IsNotFiltered],0))

Result will be like

image.png

View solution in original post