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:

H_name2

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.

H_name1

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.

H_name3

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.

H_name4

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.

H_name5

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.

H_name6

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.

H_name7

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.

 

7 comments

Skip to comment form

    • Avatar
    • Újszászy András on 2016-03-22 at 13:42
    • Válasz

    Nagyon köszi a cikket!
    Nagyon tetszik az ötlet.

    Azon gondolkoztam, hogyan lehetne kicsit talán rugalmasabbá tenni, és a HIPERHIVATKOZÁS függvény segítségével talán könnyebben lehet dinamikus linkeket generálni.

    Az alap felállás ugyanaz, csináltam egy listát a munkalap nevekkel, amiből készítettem egy legördülő menüt. A példámban az “A2” cellába van a sheet kiválasztó gomb.

    A “B2” cellába kézzel meg lehet adni, melyik cellára ugorjon a hiperlink függvény.

    maga a képlet így néz ki, amit én a “D2”-be tettem, de bárhol lehet :
    =HIPERHIVATKOZÁS(“#”&A2&”!”&B2;”>>>”)

    A # azt jelzi, hogy a munkafüzeten belül mozogjon, a A2&”!”&B2 paraméter összefűzi a link további paramétereit, és a második argumentum a “rövid név” azaz friendly name, pedig “>>>” értéket kapott, ez fog megjelenni a cellában.

    Ezzel a módszerrel el lehet kerülni az INDIREKT volitális függvények használatát

    1. Köszönöm a kommentet!
      Nagyon jó megoldás a tied is, köszi, hogy megosztottad.
      A volatilis INDIREKTtel kapcsolatban: az említett esetben a függvény csak elnevezésben van használva, nem a munkalapon. A volatilitás csak a munkalapon értelmezhető, mert az elnevezés akkor “kalkulálódik”, amikor hivatkozunk rá.

      • Avatar
      • Kovács Gábor on 2017-10-28 at 09:05
      • Válasz

      Kedves András!
      Bár több, mint egy évvel ezelőtti a cikk, de én csak most kerültem ilyen probléma elé.
      A megoldás kitűnő, és nagy mennyiségű adatnál gyakorlatilag csak ez a jó, hiszen ott minden hivatkozott fájl esetében bele kellene körmölni legalább a fájl nevét az adott cellához.
      Egy csomó munkától mentettél meg!
      Az én esetemben egy olyan adatbázist kellett létrehoznom, ahol több ezer fénykép (jpg) fájlt kell csatolnom az adott nevekhez. A jpg-ket egy mappába tettem az Excellel. A fájlok nevét a tárgy – melytől a kép készült – elhelyezkedése (pozíciója) alapján átneveztem, (számoztam), és ezt a nevet az Excelben generáltam össze. Így a link teljesen automatikusan keletkezik, sőt, amennyiben változik a pozíció és át kell nevezni a jpg-t a link követi a fájlt. Ha az egészet másik mappába helyezem a =CELLA(“filenév”) -ből generált mappa követi a jpg elhelyezkedését is.
      Úgyhogy még egyszer köszi a dinamikus ötletet.
      Gábor

    • Avatar
    • Bottlik Gábor on 2017-04-01 at 21:23
    • Válasz

    Kedves Kris!

    Ez a legördül menüből link gyártás nagyon parádés. Eltettem magamnak a későbbiekre. 🙂
    Viszont van egy problémám, ami ide tartozik, ide tartozhat, már ha legördülő listához kapcsolódik.
    Adott egy lista, a lista az A1:A10 tartományban található, aminek a neve – egyszerűség kedvéért – LISTA.
    Van egy másik táblázat (ADATBEMENET, másik munkalap B1:B20 tartománya), ahol egymás alatt több cellába is a LISTA elemeiből kell kiválasztani adatot. Odáig eljutottam, hogy a cellákat az érvényesítés pontban található opciók segítségével megadtam a LISTA halmazt, mint egyedül érvényes bemenetet. Még az üresek átugrását is bepipáltam. A csavar a következő: LISTA elemeit egyszer és csak egyszer választhatom ki. Még ezt is sikerült megoldanom a LISTA elemei táblázatban ellenőrzőm, hogy adott elem szerepel-e már a “bemenet” táblázatban ( LISTA első cellájának tartalma: =HA(DARABTELI(ADATBEMENET;”lista elem1″)>=1;””;”lista elem1″) ). Sajnos hiába választom ki a LISTA egyik elemét, a következő cella legördülő listájában kihagyni, még kihagyja, de a listában üres hely marad:
    Értsd: B3 cellában már kiválasztottam a “lista elem3″ tételt, de a B6 cellában nem azt találom, hogy kihagyás mentesen jönnek a lista elemek, annak ellenére, hogy be van pipálva az üresek átugorja opció(!), hanem a következőt:
    lista elem1
    lista elem2

    lista elem4

    Kérdésem, hogy rosszul értelmezem az üres fogalmát, vagyis =”” nem egyenlő az üres, így azt nem is hagyja ki a listából?
    Illetve, hogy van-e olyan megoldás, amivel ezt a dolgot meg tudom csinálni, vagyis a már kiválasztott elem egy másik cellánál a kiválasztáskor meg se jelenjen és a helyén se legyen üres sor?
    Remélem sikerül leírnom a érthetően a problémát.
    Segítséged előre is köszönöm.
    Üdvözlettel:
    Bottlik Gábor

    1. Szia!
      Picit hosszú lenne elmagyarázni – valóban az üres az nem mindig úgy üres, ahogy gondoljuk.
      Itt találsz egy fájlt, egy lehetséges megoldással. Remélem, erre gondoltál.
      Üdv:
      Kris

    • Avatar
    • Bottlik Gábor on 2017-05-20 at 15:56
    • Válasz

    Kedves Krisz!

    Ez az!
    Nagyon köszönöm a megoldást.
    Üdvözlettel:

    Bottlik Gábor

    1. Nagyon szívesen, örülök, hogy hasznos!

Vélemény, hozzászólás?

Your email address will not be published.

css.php