Hyperlink és legördülő lista kombinálása

A hyperlink egyszerű, gyakran használt eszköz a munkafüzetben való navigáció megkönnyítésére: könnyű vele egyik munkalapról a másikra ugrani. Gondoltatok rá, hogy ha sok munkalapunk van, milyen egyszerű lenne kiválasztani egy legördülő listából, hogy hová akarunk ugrani? Nem kell nagy helyet elfoglaló link-listát fenntartani minden munkalapon: csak kiválasztod és rákattintasz – ahogy az ábrán látod: a munkalap nevek olasz városok, ezek jelennek meg a legördülő listában:

Igen, ezt meg lehet csinálni – mutatom, hogyan!

Alapvetés

Először nézzük, hogyan működik a hyperlink beszúrás! A Hyperlink beszúrása menüpontban nem csak munkalap cellákat, hanem elnevezett tartományokat is ki lehet választani – így ezekhez egyszerűen kapcsolható hyperlink, ami rögtön a tartományhoz ugrik. Azonban ha jobban megfigyelitek, nem jelennek itt meg azok az elnevezések, amelyek mögött formula áll – hiába ad vissza tartományt a formula. Csak a „statikus”, közvetlen cella referenciát tartalmazó neveket lehet itt a hyperlinkhez adni.

Kérdés tehát: Hogyan tudunk hyperlinket kapcsolni olyan elnevezéshez, ami formulát tartalmaz?

Kicsit be kell ehhez csapni az Excelt: az elnevezést azután változtatod meg, hogy hyperlinkként használtad! Tehát:

1. először csinálj egy elnevezést, ami tartományra hivatkozik
2. tedd bele a hyperlinkbe
3. majd változtasd meg az elnevezést: írd be a formulát, amit használni szeretnél.

Ez lesz a megoldásunk alapja. Nézzük lépésről-lépésre, hogyan lehet felépíteni a legördülő listával kombinált hyperlinket!

1. Munkalap nevek listája

Szükséged lesz egy elnevezett tartományra, ami a legördülő listában használni kívánt munkalap neveket tartalmazza. Írd be egy munkalapra a neveket (ezek az példa szerint az olasz városnevek) és nevezd a tartományt Sheets_List -nek.

2. Érvényesítési lista

Fontos, hogy minden munkalapon ugyanabba a cellába kerüljön majd az érvényesítés! Az egyik munkalapon, ahol használni akarod a trükköt, készíts érvényesítést (Data validation) legördülő listával, a Sheets_List nevet használva.

Ez eddig ugye semmi extra: ki lehet választani egy munkalap nevet a listából, és nem történik semmi. Nézzük tovább!

3. Elnevezés készítése a hyperlinkhez

Csinálj egy elnevezést My_Sheet_Choice névvel, ami az aktuális aktív cellára hivatkozik – ezt fogjuk majd megváltoztatni.

4. Hyperlink felrakása a cellára

Szúrd be a hyperlinket a cellára, ahova a legördülő listát tetted! A hyperlink a My_Sheet_Choice névre mutasson – ahogy az alábbi ábrán látod.

5. A trükk: hivatkozás átírása

Változtasd meg a hivatkozást a My_Sheet_Choice elnevezésben! Olyan formulát fogunk berakni a név referenciájába, ami az adott legördülő listában kiválasztott munkalap celláját adja vissza – így fog a hyperlink a megfelelő munkalapra ugrani.

=INDIRECT( ADDRESS( 1,2,,,INDIRECT( ADDRESS(1,2) ) ) )

=INDIREKT( CÍM( 1;2;;;INDIREKT( CÍM(1;2) ) ) )

Figyelem! a fenti formula a példában bemutatott esethez tartozik, amikor a B1 cellában van a legördülő lista. Ha te máshova tetted, mindkét CÍM formulában kell átírnod a sor és oszlop számot: első paraméter a sor, második az oszlop száma: 1;2 -> B1.

6. Hyperlink másolása

Minden munkalapra, ahol használni szeretnéd a legördülő listás hyperlinket, másold át a cellát, ami a trükkös linket tartalmazza. Figyelj, hogy minden munkalapon ugyanoda kerüljön a linkes cella!

Hogyan működik?

A beágyazott INDIREKT( CÍM(1;2) ) formula kiolvassa a munkalap nevet, amit a B1 cellába írtál. (B1-nek a CÍM formulában az 1;2 paraméter felel meg.) Ez a munkalap név lesz a külső CÍM formula 5. paramétere. Az első két paraméterrel együtt, ami itt is az 1;2 (sor;oszlop) hivatkozás, a formula cella referenciát ad vissza. Összerakja a cellát és a munkalap nevet a megfelelő (szöveges) formátumba. Például: ‘New York’!$B$1 -> aposztrófok közé kerül a munkalap név, és felkiáltójel jön a cellahivatkozás elé. Ebből a szöveges referenciából csinál az INDIREKT valódi cella hivatkozást, amit aztán az elnevezett tartományon keresztül megkap a hyperlink, így ez a dinamikus hivatkozás fogja a hyperlinket a megfelelő munkalapra vezetni.

Innen letölthetitek a példa fájlunkat, amiben fel van építve egy működő modell.

Az elnevezett tartományok alapjairól itt olvashatsz.

Ez az írás egy korábbi angol cikkünk alapján készült.