Hvis du trenger å lage cellereferanse, hvor referansen i seg selv skal være dynamisk (altså at du på forhånd ikke vet hvilken celle referansen skal peke til), så kan du bruke en kombinasjon av funksjonene indirekte() og adresse().
Funksjonen indirekte() lar deg angi en cellereferanse som ren tekst, og funksjonen adresse() lar deg bygge opp en cellereferanse gjennom å angi både ark, rad og kolonne. Ved å bruke disse på formen =indirekte(adresse()), så kan man altså lage helt dynamiske cellereferanser.
Her er et par eksempler på bruksområder.
Oppslag
Dersom du har 12 ark som er identiske, ett ark for hver måned, hvor det er en matrise som viser salgstall fordelt på selger (rader) og produkt (kolonne). Du skal lage et oppslag som henter inn salgstall for valgt måned, selger og produkt.
I arket for oppslaget, så trenger du tre celler for å angi henholdsvis måned, selger og produkt. Disse kan du med hell basere på datavalidering|liste.
Formelen i B5 for å hente frem salgstall må altså hente verdien i Januar!C3. Dette kan vi bygge opp via adresse(), og så pakker vi alt inn i indirekte().
=INDIREKTE(ADRESSE(1+SAMMENLIGNE(B2;Januar!A2:A4;0); 1+SAMMENLIGNE(B3;Januar!B1:D1;0);;;B1))
Merk: Funksjonen sammenlikne() vil returnere at Per = 2 og Vare B = 2 (som i utgangspunktet tilsvarer celle B2), men ettersom både rad 1 og kolonne A i salgsoversiktene går med til overskrifter, så må vi plusse på 1 slik at adresse() peker mot C3.
Offset – peke på en celle ved siden av
En relativ cellereferanse (altså en cellereferanse uten $) vil nødvendigvis oppføre seg relativt til autofyll – derav navnet. Drar du autofyll ett hakk til høyre, så flytter cellereferansen seg ett hakk til høyre.
Men hva hvis du vil ha en formel som alltid peker på cellen tre rader over og to rader til høyre, uavhengig av hvilken celle du setter formel i? Igjen kan du bruke indirekte(adresse()), men denne gang i kombinasjon med rad() og kolonne().
Denne formelen vil alltid hente verdien tre rader over og to kolonner til høyre:
=INDIREKTE(ADRESSE(RAD()-3;KOLONNE()+2))
Merk: Negativt tall bak rad betyr opp, positivt tall betyr ned. Negativt tall bak kolonne betyr til venstre, positivt tall betyr høyre.