Valószínűleg a következő feladatot megoldani sokkal egyszerűbb lenne egy VBA-kódsorral, de miután az Excel formuláival elérhető lehetőségek tárháza végtelen és jómagam is mindig függvényekben gondolkodom először, a mai posztban egy automatikusan frissülő Data Validation Listet fogok bemutatni, amelynek legördülője menüje a gépelésünktől függően változik.

Normál esetben ugyebár valahogy így néz ki a Data Validation Listünk - van egy lista, ez van a legördülő menüben és görgethetünk bőszen, ha hosszú listáról van szó:

Azt szeretnénk tehát elérni, hogy a Data Validation Listet tartalmazó cellában ha begépelünk egy pár betűt, akkor az alapján a legördülő menü már csak azt ezt a részletet tartalmazó eredményeket mutassa. Első lépésként ehhez kapcsoljuk ki a Data Validationt:

Az értékeket tartalmazó cellák elé szükségünk lesz egy segédoszlopra, amely egy első ránézésre komplikáltnak tűnő képletet fog tartalmazni:

=IF(ISNUMBER(SEARCH($E$3,$I$3:$I$16)),MAX($H$2:H2)+1,0)

Mit is csinálunk itt és leginkább miért? A képletünk magja a SEARCH függvény, ami ugyebár az első paramétereként megadott szöveget, szövegrészt, betűt fogja megkeresni a második paramétereként megadott szövegben és ha megtalálja, visszaadja azt a karakterszámot, ahol ez kezdődik. Jelen esetben tehát a data validation listet tartalmazó cellába beírt karaktereket fogja a data validation listünk értéklistájában megkeresni és ha az adott cellában megtalálja a szövegrészt, akkor visszadob egy számot, ahányadik karakternél ez a rész kezdődik. Egy szimpla ISNUMBER segítségével pedig TRUE vagy FALSE értékre alakítjuk az eddigi eredményeket, tehát összefoglalva lesz egy TRUE értékünk azon cellák előtt amelyben az általunk begépelt karakterekből talált a SEARCH és egy FALSE azok előtt, ahol nem.

Végül pedig a listánk sorrendisége okán csinálunk még egy IF függvényt, azaz ha az eddigiek eredménye TRUE volt, akkor vegye a legelső cellától az aktuális celláig terjedő intervallumot, majd az ebben a tartományban lévő legmagasabb értékhez adjon hozzá egy egyest - ellenkező esetben legyen nulla az eredmény. Ahogy látható a fenti képen is, ezzel azt értük el, hogy szép növekvő számsorrendben jelennek majd meg azok a cellák, ahol a begépelt szövegrészünk megtalálható.

Most következik az a lépés, hogy ebből megcsináljuk a folyamatosan frissülő Data Validation Listet.

Menjünk át a munkafüzetünkön egy üres helyre, majd építsünk fel egy ilyen képletet:

=IFERROR(VLOOKUP(ROWS($K$3:K3),H3:I16,2,0),"")

Ennek a képletnek a lelke a ROWS függvény, ami a paramétereként megadott tartományban megmutatja, hogy az adott cella hányadik sorban szerepel - jelen esetben tehát $K$3:K3 tartományban a K3 cella az 1, a $K$3:K4 tartományban K4 a kettő és így tovább. Utána ezt a számot fogjuk VLOOKUP-pal felkeresni az előbb létrehozott segédoszlopban és ha megtaláljuk, akkor kiíratjuk a mellette található listaelemet - így értelemszerűen annyi értékünk lesz ebben az új kis táblában, ahány helyen az előbb a SEARCH megtalálta a gépelt szövegrészünk.

Aztán már csak annyi kell, hogy egy IFERROR segítségével az N/A-kat üres cellára írjuk át.

Az eredmény pedig ez lett:

Tehát ha beírom a San szócskát a Data Validation Listre kijelölt cellába, akkor a segédoszlopban három helyen lesz számunk növekvő sorrendben és így a VLOOKUP három értéket fog a második segédtáblába tölteni.

Még egy példa:

És most jön az utolsó lépés, hiszen az látható, hogy létrejött a Data Validation Listünkhöz tartozó adathalmaz és ez frissülni is fog a gépelésünk után, de a kérdés, hogy hogyan fogjuk elérni, hogy mindig csak annyi itemünk legyen a legördülő menüben, amennyi a segédtáblánkban megjelent? A választ az OFFSET és a COUNTIF párosa fogja megadni.

Én már kapásból a FORMULAS ribbonfül Defined Names szekciójában található Name Managert fogom frissíteni, ahol létrehozok egy új, nevesített tartományt, amelynek forrása egy OFFSET-COUNTIF kombó lesz:

A képletünk tehát

=OFFSET($K$3,,,COUNTIF($K$3:$K$16,"?*"))

A beágyazott COUNTIF nem csinál mást, mint megnézi, hogy az új segédtáblánk rögzített területén hány darab, bármit is tartalmazó cella található, majd visszaad egy darabszámot - az OFFSET pedig az első paramétereként megadott cellától (ez most a $K$3, a segédtáblánk legfelső cellája) annyi sorral bővíti ki a tartományt, amennyit a COUNTIF visszaadott. Az OFFSET-nek ugyebár az első paramétere a sorszám, tehát hány sorral mozogjunk, második paramétere az oszlopszám, tehát hány oszlopot lépjünk - de ezek most nekünk nem kellenek, ezért van ott két tartalom nélküli vessző, a harmadik paraméter viszont a "magasság", tehát hány soros tartományról beszélünk.

És ezzel elkészült a DL_List nevezetű nevesített tartomány, amit már csak hozzá kell tennünk egy, a DATA ribbonfül Data Tools szekciójából beszúrható Data Validation List forrásaként:

Még ne felejtsünk el átlépni ugyanezen az ablakon az Error Alert fülre, ahol ki kell kapcsolnunk a "Show error alert..." nevű checkboxot, hogy egyáltalán tudjunk gépelni a Data Validationt tartalmazó cellába:

És ezzel elkészültünk, úgy működik, mint a karikacsapás: