Excel funktion som hämtar och påför värden mellan arbesblad

[BBear]

Hej!

 

Vänder mig till er med expertis inom Excel.

 

Jag står inför en utmaning i Excel där följande skall uträttas:

 

------------------------------------------------------------------------

Värden i Cellerna [specified cells] från Arbetsblad [y] skall hämtas och visas i Arbetsblad [z] i Cellerna [specified cells].

 

Ovanstående skall triggas av att man ur en lista väljer t.ex. "Kontrakt 1", varpå "Kontrakt 1"-värden hämtas ur dess arbetsblad och visas i ett annat arbetsblad samt skall dess värden landa i de celler jag kallar [specified cells].

------------------------------------------------------------------------

 

Är jag tydlig nog? Om inte så hojta till - jag hänger i denna tråd hela dagen

 

 

Mvh

Daniel

[Monshi]

Det låter som att du har en arbetsbok med ett antal blad i på vilka du har ett antal olika fakturor/kontrakt och att du vill, via ett val i lista, kunna ta fram data från ett visst blad.

 

jag antar vidare att dessa blad med data då ser likadana ut, att du vet vilken cell som ett visst värde står i.

 

Låt oss nöja oss med det, jag antar inte mer, jag gör inget mer utan att du bekräftar/berättar lite mer.

[BBear]

Det låter som att du har en arbetsbok med ett antal blad i på vilka du har ett antal olika fakturor/kontrakt och att du vill, via ett val i lista, kunna ta fram data från ett visst blad.

 

jag antar vidare att dessa blad med data då ser likadana ut, att du vet vilken cell som ett visst värde står i.

 

Låt oss nöja oss med det, jag antar inte mer, jag gör inget mer utan att du bekräftar/berättar lite mer.

 

Hej Monshi!

 

Det är helt korrekt. Jag vet vilka blad det är, vilka celler värdena finns i och till vilka destinationer de skall kopieras till. Allt detta utifrån ett val från t.ex. en droplist.

 

 

Mvh

Daniel

[Monshi]

Då börjar jag med att säga:

Dum lösning!

Bättre att spara data i en tabell och via val ta fram data till en rapport än att spara alla data i rapporterna.

imho.

 

Men men, ditt problem går att lösa.

Du söker efter funktioner INDIREKT till vilken du kan ge en adress som en sträng och INDIREKT skapar en korrekt referens/hämtar värdet.

 

För jag antar att du menar att värden ska hämtas via formler och inte kopieras in.

 

Dvs

1: Du väljer ett värde i en lista

2: Du läser med formel av värdet och skapar en grundreferens, som ser ut om

'Blad1'!

säg att denna står i cell D1

3: På resten av bladet har du formler i still med

=INDIREKT(D1& A2)

varvid värdet i cell A2 på blad1 hämtas.

 

man kan även tänka sig att man bygger upp en referens över ett större område, via ett namn, och hämtar värden ur området med formler som INDEX eller FÖRSKJUTNING.

 

är jag på rätt väg?

[BBear]

Då börjar jag med att säga:

Dum lösning!

Bättre att spara data i en tabell och via val ta fram data till en rapport än att spara alla data i rapporterna.

imho.

 

Men men, ditt problem går att lösa.

Du söker efter funktioner INDIREKT till vilken du kan ge en adress som en sträng och INDIREKT skapar en korrekt referens/hämtar värdet.

 

För jag antar att du menar att värden ska hämtas via formler och inte kopieras in.

 

Dvs

1: Du väljer ett värde i en lista

2: Du läser med formel av värdet och skapar en grundreferens, som ser ut om

'Blad1'!

säg att denna står i cell D1

3: På resten av bladet har du formler i still med

=INDIREKT(D1& A2)

varvid värdet i cell A2 på blad1 hämtas.

 

man kan även tänka sig att man bygger upp en referens över ett större område, via ett namn, och hämtar värden ur området med formler som INDEX eller FÖRSKJUTNING.

 

är jag på rätt väg?

 

 

Hej igen!

 

Jag har mina invändningar mot grundförutsättningarna också och visst är det inget önskvärt utgångsläge. Men med det sagt så måste jag ändå arbeta utifrån det jag har och "kan" inte ändra på dessa förutsättningar.

 

Du är på helt rätt väg. Det rör sig enbart om "LOOKUP" och inte "COPY". Värden skall inte kopieras, endast pekas på och presenteras i ett annat blad, i angivna celler.

 

Jag har inte god koll på syntaxen för Excelfunktionalitet/Visual Basic och vet inte hur jag skall uttrycka mig för att påbörja detta. Hur skulle koden för det scenario som du beskriver, se ut?

 

 

Tack för dina rappa svar!

 

//Daniel

[Monshi]

VBA kod behövs inte, bara formler.

 

Här får du två exempel på hur det kan se ut. Ena skapar en grundsträng som formlerna sedan använder för att hämta data, andra skapar en namngiven referens som formlerna.

 

Detta är grunden, om du vill kunna välja blad med en dropdown måste du bygga upp en referens till det önskade bladet och det gör du via INDIREKT.

 

Se bifogad fil.

 

vilken version av Excel?

Exempel.xls

[BBear]

VBA kod behövs inte, bara formler.

 

Här får du två exempel på hur det kan se ut. Ena skapar en grundsträng som formlerna sedan använder för att hämta data, andra skapar en namngiven referens som formlerna.

 

Detta är grunden, om du vill kunna välja blad med en dropdown måste du bygga upp en referens till det önskade bladet och det gör du via INDIREKT.

 

Se bifogad fil.

 

vilken version av Excel?

 

Ok, då skall det nog gå att lura ut med hjälp av dina exempel. Vilket jag för övrigt uppsakttar enormt att du bidragit med!

 

 

Jag har Excel 2003 (Engelskspråkig). Så vad kallas INDIREKT i den engelska utgåvan?

 

 

 

Mvh

Daniel

[BBear]

Jag förtydligar ytterligare

 

Bladet som all data hämtas ifrån är alltid detsamma. Det medför att det som skall variera i referensen är inte bladet utan de celler som finns i bladet. Men i droplistan vill jag naturligtrvis inte speca alla celler som den skall peka på, utan namge den till "kontrakt1", "kontrakt2" etc etc.

 

De celler som skall presentera dem data som hämtats från bladet med kontrakten i, är alltid statiska vad gäller placering, men skall visa annat värde när annat kontrakt valts i droplistan.

 

Förvirrar jag allt och alla nu??

 

 

 

Mvh

Daniel

[Monshi]

Det förändrar ju allt.

 

Du menar att du har, säg, ett kontrakt mellan rad 1 och 50, ett på rad 51 till 100 osv.

Är de statiska i höjd...

 

nä men ärligt, skapa ett exempel och ladda upp här.

Då ser jag vad du har, vad du vill ha.

[BBear]

Det förändrar ju allt.

 

Du menar att du har, säg, ett kontrakt mellan rad 1 och 50, ett på rad 51 till 100 osv.

Är de statiska i höjd...

 

nä men ärligt, skapa ett exempel och ladda upp här.

Då ser jag vad du har, vad du vill ha.

 

Jag misstänkte det... Som sagt, det är inte helt lätt att förklara när jag saknar grunder i Excel.

 

Kontrakten ligger på rad och man kan på så sätt räkna ut vilken cell som skall hänvisas till vid val av ett kontrakt.

 

Se exempel Observera att den data som skall presenteras, skall alltså presenteras lite "här och där", men dock vid fasta statiska platser.

Kontrakt_exempel.xls

[Monshi]

jaha, en tabell.

med index

med poster

Det ser ju ut precis som det ska.

Varför sade du inte det från början

 

Nu är det ju enkelt.

Bara två steg

1: Hitta vilken rad valt kontrakt finns på.

2: Hämta data

 

1: =MATCH(J3;Kontrakt!A:A;0)

(kanske du behöver byta ; mot ,)

2: =INDEX(Kontrakt!B:B;Utdata!L3)

för att hämta serienumret givet att formel 1 är skriver i L3

 

Att ha formel 1 i egen cell är trevligt men du kan givetvis används VLOOKUP också

=VLOOKUP($J$3;Kontrakt!$A:$I;2;FALSE)

hämtar även denna serievärdet för valt kontrakt.

[BBear]

jaha, en tabell.

med index

med poster

Det ser ju ut precis som det ska.

Varför sade du inte det från början

 

Nu är det ju enkelt.

Bara två steg

1: Hitta vilken rad valt kontrakt finns på.

2: Hämta data

 

1: =MATCH(J3;Kontrakt!A:A;0)

(kanske du behöver byta ; mot ,)

2: =INDEX(Kontrakt!B:B;Utdata!L3)

för att hämta serienumret givet att formel 1 är skriver i L3

 

Att ha formel 1 i egen cell är trevligt men du kan givetvis används VLOOKUP också

=VLOOKUP($J$3;Kontrakt!$A:$I;2;FALSE)

hämtar även denna serievärdet för valt kontrakt.

 

Tack och åter igen tack! Riktigt så enkelt som exemplet jag bifogade är det inte, men på ett ungefär i alla fall Nu börjar jag få styr på grejerna i alla fall.

 

Dock så inser jag att kontraktsnumren kan variera i storlek. Säg att det kontrakt som ligger överst i tabellen har nummer 5022, men kontrakt under detta har värde 6030 eller 4150. Det rör till det hela då MATCH går mot kontraktsnummer (?). Kan man gå på radnummer istället? Siffrorna 1, 2, 3, 4, 5, 6 var bara exempel alltså.

 

 

Så förtdydligar jag det lite:

 

Contract 1 = Contract 5022

Contract 2 = Contract 6030

Contract 3 = Contract 4150

.

.

.

.

 

Det kan vara vilka nummer som helst.

 

Mvh

Daniel

[Monshi]

Du vill välja mellan enkla siffror, (1, 2, 3) i listan men hämta längre siffror (5022, 6030)

 

Inget problem. Det MATCH gör är ju leta reda på ett index (en rad) som INDEX kan hämta värden ifrån. Det enda som behövs om du vill hämta relativt en viss rad är att justera värdet som slås upp så att det blir rätt för att passa INDEX. Bort med MATCH, bara en enkel formel där som räknar upp.

 

Klart.

 

Var det det du menade denna gång?

[BBear]

Du vill välja mellan enkla siffror, (1, 2, 3) i listan men hämta längre siffror (5022, 6030)

 

Inget problem. Det MATCH gör är ju leta reda på ett index (en rad) som INDEX kan hämta värden ifrån. Det enda som behövs om du vill hämta relativt en viss rad är att justera värdet som slås upp så att det blir rätt för att passa INDEX. Bort med MATCH, bara en enkel formel där som räknar upp.

 

Klart.

 

Var det det du menade denna gång?

 

 

God morgon!

 

Jag tror jag hänger med på vad du menar. Då skulle man på förhand ha en formel i "Contract" cellerna som INDEX kan hitta och plocka värde från? Hur skulle det se ut i "Excelsyntax"?

 

 

Mvh

Daniel

[Monshi]

=INDEX(Kontrakt!B:B;$J$3+2)

räcker. Givet mallen/exemplet du bifogade tidigare.

 

Det jag menade var att man kanske ska ja J3+2-delen i en egen cell bara för att frikoppla radindexet så att säga men det där går lika bra. Om man vill byta till en PASSA/MATCH-sats senare vore det då bara att ändra i den cellen.

[BBear]

=INDEX(Kontrakt!B:B;$J$3+2)

räcker. Givet mallen/exemplet du bifogade tidigare.

 

Det jag menade var att man kanske ska ja J3+2-delen i en egen cell bara för att frikoppla radindexet så att säga men det där går lika bra. Om man vill byta till en PASSA/MATCH-sats senare vore det då bara att ändra i den cellen.

 

 

Klockrent! Nu använder jag bara {=INDEX(Kontrakt!B:B;$J$3+2} och det funkar ypperligt TACK!!

 

Och nu till den sista funderingen. Den droplistan du skapade i ditt exempel som innehåll "1" och "2" - hur redigerar jag den till att innehålla fler siffror (upp till 6)?

 

 

 

Mvh

Daniel

[Monshi]

Inga måsvingar runt formeln, det indikerar matrisformel och det har du inte här

 

Listan är ett exempel på funktionen Dataverifiering. Excel 2003 sade du? Tror du återfinner denna i menyn Data. Markera cellen, ta upp dialogen via menyerna och lägg till värden.

 

Eller skriv din lista i celler på samma blad och länka till denna lista, det går också bra.

[BBear]

Inga måsvingar runt formeln, det indikerar matrisformel och det har du inte här

 

Listan är ett exempel på funktionen Dataverifiering. Excel 2003 sade du? Tror du återfinner denna i menyn Data. Markera cellen, ta upp dialogen via menyerna och lägg till värden.

 

Eller skriv din lista i celler på samma blad och länka till denna lista, det går också bra.

 

 

Taffligt försök av mig att markera formeln från texten i mitt inlägg

 

Nu hittade jag iaf dialogfönstret för Dataverifiering och har ändrat till önskat antal värden.

 

 

 

Som jag sagt tidigare: Tack för all din hjälp och att du faktiskt tar dig tid att svara på alla dessa eländiga frågor. Fortsätt så !

 

 

Mvh

Daniel

[BBear]

Det har uppstått en önskan av tillägg i funktionalitet (surprise?)

 

Man vill förutom att välja kontrakt och få dess motsvarande värden visade i "Blad2", även få en tabell, som redan existerar och beräknas utifrån ett kontrakts värden, bli presenterat i "Blad2".

 

Alltså: I Blad1 finns kontrakten och tillhörande tabeller (som kallas Betalningsprofiler). Då man i Blad2 väljer ett kontrakt så skall tillhörande betalningsprofil också hämtas från Blad1. Problemet är att dessa tabeller ligger horisontellt på rad (och måste så göra) och kan således inte använda mig av den ursprungliga INDEX (?)

 

Bifogar exempel nedan.

 

 

Mvh

Daniel

Kontrakt_exempel2.xls

[Monshi]

Om det finns en direkt koppling mellan tabell och kontrakt behövs ingen valruta, det går att söka reda på rätt tabell givet att de ligger så som de nu ligger. Jag kan tänka mig att när antalet kontakt ökar blir det formatet på tabell ohållbart.

 

Iochmed att varje tabell med betalningsprofil är kopplad till enbart ett kontakt borde väl dessa data egentligen ligga i samma tabell som kontrakten?

 

Eller alternativt i en egen tabell. Finns ju ingen anledning att ha dem uppställda som ni nu har det egentligen. Ett enkelt råd är att man ska skilja på data och presentation. Data ska helst sparas i så enkla strukturer som möjligt med presentationen ska vara lätt och ledig att ta åt sig.

 

ja varför inte i en egen tabell? En vanlig rak tabell med kontraktsnummer som nyckel i kolumn ett.

 

Som sagt, om du vill ha denna modell som du har på bladet nu går det att lösa. Det blir dock en enklare lösning om du kan göra en vanlig tabell av det istället.

[BBear]

Om det finns en direkt koppling mellan tabell och kontrakt behövs ingen valruta, det går att söka reda på rätt tabell givet att de ligger så som de nu ligger. Jag kan tänka mig att när antalet kontakt ökar blir det formatet på tabell ohållbart.

 

Iochmed att varje tabell med betalningsprofil är kopplad till enbart ett kontakt borde väl dessa data egentligen ligga i samma tabell som kontrakten?

 

Eller alternativt i en egen tabell. Finns ju ingen anledning att ha dem uppställda som ni nu har det egentligen. Ett enkelt råd är att man ska skilja på data och presentation. Data ska helst sparas i så enkla strukturer som möjligt med presentationen ska vara lätt och ledig att ta åt sig.

 

ja varför inte i en egen tabell? En vanlig rak tabell med kontraktsnummer som nyckel i kolumn ett.

 

Som sagt, om du vill ha denna modell som du har på bladet nu går det att lösa. Det blir dock en enklare lösning om du kan göra en vanlig tabell av det istället.

 

Hej igen Monshi!

 

Det finns några steg som inte visas i mitt exempel där Betalningsprofilerna hämtar data från fler källor än bara kontraktstabellen. Vi kan säga att mitt mål är "bara" att få en redan spagettiliknande arbetsbok till att bli ännu mer spagettliliknande pga utökad presentation

 

Så - utgå från det värsta med att ingen ändring kan göras på förutsättningarna. Allt skall stå som det gör...tyvärr.

 

Det positiva är att det aldrig kommer bli mer än 6 kontrakt med tillhörande 6 Betalningsprofiler totalt.

 

 

Mvh

Daniel

[Monshi]

Okej, jag ger lösningsexempel, du implementerar och anpassar

 

Vi är tillbaka på PASSA/MATCH. Denna kan söka i endera rader eller kolumner.

Vi vet att alla tabeller ser lika ut, vi behöver bara finna var på bladet, vilken kolumn, tabellen vi söker finns i

 

=MATCH(C8;Sheet1!A26:Q26;0)

ger dig kolumnen/indexet inom referensen där tabellen/kontraktsnumret står.

Därefter är det, som förut, INDEX som hämtar.

För första raden blir det

=INDEX(Sheet1!A28:Q28;Sheet2!$L$17)

Kopiera nedåt.

 

Känner du igen det? Liknar det något jag visat tidigare?

Fundera på vad formlerna gör.

[BBear]

Okej, jag ger lösningsexempel, du implementerar och anpassar

 

Vi är tillbaka på PASSA/MATCH. Denna kan söka i endera rader eller kolumner.

Vi vet att alla tabeller ser lika ut, vi behöver bara finna var på bladet, vilken kolumn, tabellen vi söker finns i

 

=MATCH(C8;Sheet1!A26:Q26;0)

ger dig kolumnen/indexet inom referensen där tabellen/kontraktsnumret står.

Därefter är det, som förut, INDEX som hämtar.

För första raden blir det

=INDEX(Sheet1!A28:Q28;Sheet2!$L$17)

Kopiera nedåt.

 

Känner du igen det? Liknar det något jag visat tidigare?

Fundera på vad formlerna gör.

 

 

Otroligt vilken god hjälp du bistår med Där satt den!

 

Kunskapen du förmedlat är ej förgäves! Jag förstår nu vad formlerna gör och hur de förhåller sig till varandra (INDEX/MATCH).

 

MATCH returnerar det värde i vilken cell den får träff på valt kontraktsnummer. KOntraktsnumret får den från cell C8. Därfeter söker MATCH i Sheet1 på rad 26 inom spannet kolumn A till Q och får då träff. Eftersom kontraktet befinner sig i andra cellen från vänster returneras värde 2.

 

INDEX greppar sedan det värde som befinner sig i cell 2 på rad 28. Cell 2 tar den ifrån det returnerade värdet från MATCH.

 

Har jag fått någorlunda grepp om det?

 

 

Mvh

Daniel

[Monshi]

Jupp, på ett ungefär är det korrekt.

 

För att vara mer exakt.

 

MATCH söker igenom den vektor den får (A26 till Q26 i exemplet) och ger som svar den relativa positionen inom denna vektor där det som söks efter finns.

Notera det. MATCH ger inte rad/kolumn-numret.

 

Testa och lek lite med den, detta är en av de vanligaste frågorna/svaren här.