Dynamisk valg af ark og celler
I dette tip ser jeg igen på en udfordring, jeg fik på nettet.
En projektmappe indeholder et antal ark, i dette tilfælde et ark for hvert år i en årrække. Disse ark er navngiver fx Salg2018, Salg2019, Salg2020 og så fremdeles. Indkøb2018, Indkøb 019 og så videre samt Bonus2018, Bonus2019 osv. Altså tre forskellige typer arkindhold fra en årrække. Nu ønskes det, at der dynamisk skal kunne vælges et generisk "arknavn" i én rulleliste, et årstal i en anden rulleliste og endeligt en given celle i hvert ark, ud fra en på forhånd defineret liste over mulige celler i en tredje rulleliste - altså tre rullelister i alt. Indholdet af den valgte celle i det valgte ark skal vises i en celle i samme ark som rullelisterne er oprettet i, her kaldet Resultat.
Rullelisterne laves med datavalidering, som er beskrevet i artiklen Datavalidering og i alle tre tilfælde er den tilladte datatype en liste. Den første rulleliste skal indeholde de generiske arknavne, altså Salg, Indkøb og Bonus. Den næste skal indeholde de relevante årstal, fx2017, 2018, 2019, 2020, 2021 og den sidste skal indeholde de celler i hvert ark, der skal kunne vælges. Da ikke alle celler i hvert ark skal kunne vælges, skal de enkelte ark været opbygget efter samme skabelon, og dcet skal specificeres, hvilke celler man ønsker at kunne se indholdet af i arket resultat. I eksemplet antager jeg, at den sidste rulleliste skal indeholde cellerne A1, A2, B3, B4 og C5 (antallet er faktisk ubegrænset). Der kan også være forskellige celler afhængigt af hvilket generisk arknavn, der vælges, soå man kan vælge nogle celler i arket Salg, andre i arket Indkøb og så fremdeles, men det vil kræve at den sidste rulleliste gøres afhængig af valget i en foregående. Det vil jeg ikke komme ind på i denne artikel, men det er beskrevet i artiklen Afhængige dropdownlister.
Vi har nu et regneark med tre rullelister, eksemplet placeret i A1 (generisk navn), B1 (årstal) og C1.(de valgbare celler i arkene). Vi skal nu bruge en enkelt hjælpecelle. Denne kan placeres hvor som helst i regnearket, men for overskuelighedens skyld, har jeg her placeret den D1. Så mangler vi bare formlen, som skal vise resultatet af de valg vi foretager i rullelisterne, her i E1.
For nemheds skyld ( min nemheds skyld), har jeg ikke brugt årstal, men bare tal, som kvalifikation på det generiske navn, altså Salg1, Salg2 osv. i stedet for Salg2017, Salg2018 og så videre. Desværre kan man ikke på samm e tid få vist rullelistepilene i alle celler med rullelister da de kun vises, når en celle med rulleliste er aktiv, men i eksemplet er der altså rullelister i de markerede celler. D1 er hjælpecellen som indeholder denne simple formel, som bare sætter generisk navn sammen med det valgte årstal
=A1&B1
altså en konkatenering (sammenkædning) af indholdet af valgene i de to første rulleliste (hvor rulleliste 2 altså burde have indeholdt årstal og ikke bare tal.*
E1 indeholder formlen, der viser resultatet. Denne formel er baseret på funktionen Indirekte(), som bruges til at "oversætte" indholdet af en celle til en reel reference.
=INDIREKTE("'" & D1&"'!"&C1)
Egentlig er de enkelte anførselstegn ikke nødvendige i det konkrete eksempel, da der ikke er blanke i arknavnene, men havde der været det, skulle de have været der, så for at tage højde for, at der senere kan komme arknavne, der indeholder blanke, er de taget med her.
Så lad mig se på delene i formlen. Som det kan ses består den af en række elementer, der konkateneres med ampersand-tegn (&). Først kommer "'" - altså det enkelte anførselstegn "pakket ind" i dobbelte anførselstegn for at vise, at det skal opfattes som en tekst. Dette konkateneres med indholdet af hjælpecellen. Dette konkateneres igen med en tekst, denne gang "'!", altså det enkelte anførselstegn, som afslutter det første og så udråbstegnet, som fortæller at nu kommer der en cellereference (' og ! er pakket ind i samme dobbelte anførselstegn, da de begge er tekst og står lige ved siden af hinanden). Dette konkateneres igen med den celle (C1), der indeholde adresse på den celle i det valgte ark, vi gerne vil se. Skulle det være muligt at vælge en hvilken som helst celle i de relevante ark, kunne man have undværet rullelisten i C1, og brugeren kuunne så selv indtaste en hvilken som helst celleadresse i denne celle.
Set vi på indholdet af formlen med D1 og C1 "oversat" til de værdier, de repræsenterer, ser formlen sådan ud
Formel i E1 =INDIREKTE("'" & D1&"'!"&C1)
Med værdier vist: =INDIREKTE("'" &"Salg2'!A1")
Hvis det ikke skulle have været dynamisk, kunne hele formlen bare være skrevet som
=Salg2!A1
men så ville den skulle rettes, hver gang man vil have fat i et andet ark og/eller en anden celle.
* Jeg havde lavet eksemplet i en anden sammenhæng, og ønskede ikke at ændre indholdet af rullelisterne.