SOLVED

Get the filtered/selected value from a table.

%3CLINGO-SUB%20id%3D%22lingo-sub-1768804%22%20slang%3D%22en-US%22%3EGet%20the%20filtered%2Fselected%20value%20from%20a%20table.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768804%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20goal%20here%20is%20to%20use%20Slice%20to%20select%20a%20value%20and%20get%20that%20value%2C%20so%20I%20can%20use%20it%20in%20other%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20the%20attached%20sheet%20for%20example%2C%20explanation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1768804%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1768963%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20the%20filtered%2Fselected%20value%20from%20a%20table.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768963%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Excel%20two%20functions%20take%20hided%20content%20into%20account%2C%20SUBTOTAL()%20and%20AGGREGATE().%20You%20may%20add%20helper%20column%20as%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20415px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225821iA860AF56450408FE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20which%20function%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DAGGREGATE(3%2C3%2C%5B%40Kode%5D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ereturns%200%20or%201%20depend%20on%20row%20is%20filtered%20or%20not.%20To%20return%20first%20filtered%20value%20we%20may%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(%5BKode%5D%2CMATCH(1%2C%5BIsNotFiltered%5D%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EResult%20will%20be%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20885px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225822i5347472CBBA66470%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1769119%22%20slang%3D%22en-US%22%3ERe%3A%20Get%20the%20filtered%2Fselected%20value%20from%20a%20table.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1769119%22%20slang%3D%22en-US%22%3E%3CP%3EPerfect%2C%20thank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent 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 (Frequent 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