Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
lutsen
 | Femeie
24 ani
Cluj
cauta Barbat
24 - 48 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Source:
Array formula, just like in the example from source:
=IFERROR(INDEX($B$3:$E$12;SMALL(IF((INDEX($B$3:$E$12;;$D$16)<=$D$15)*(INDEX($B$3:$E$12;;$D$16)>=$D$14);MATCH(ROW($B$3:$E$12);ROW($B$3:$E$12));"");ROWS(B20:$B$20));COLUMNS($A$1:A1));"")
The array formula in cell B20 searches for values that meet a range criteria (cell D14 and D15), the formula lets you change the column to search in with cell D16.
This formula can be used with whatever dataset size and shape. To search the first column, type 1 in cell D16.
|
|
| pus acum 4 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Extracting data from filtered range (array formula):
=IFERROR(INDEX(Table4[Gestiune2];SMALL(IF(((SUBTOTAL(3;OFFSET(Table4[[VALID]:[VALID]];ROW(Table4[[VALID]:[VALID]])-MIN(ROW(Table4[[VALID]:[VALID]]));;1))>0)*("VALID"=Table4[[VALID]:[VALID]]));ROW(Table4[[VALID]:[VALID]])-MIN(ROW(Table4[[VALID]:[VALID]]))+1);ROW(A1)));"")
The VALID column is a column that contains the same formula ="VALID" in every cell.
|
|
| pus acum 4 ani |
|