Excel (2019) - Alternativ för Auto-fill (använda flikar)

[B.O.]

Jag har en formel som jag på ett enkelt sätt behöver återanvända (med Auto-fill-funktionen) på en stor mängd rader, men där det inbyggda sättet (default) för funktionen inte riktigt passar det jag är ute efter.

 

Formeln är en vanlig =SUMMA.OMF med ett summaområde och ett villkorsområde.

=SUMMA.OMF(Jan!D5:D40;Jan!C5:C40;"ABC")

”Jan” står för januari, som är den första fliken av sammanlagt 12 stycken flikar i en serie för alla årets månader, samtliga i samma arbetsblad.

 

Det jag vill uppnå med Auto-fillhandtaget (det lilla fyllda plustecknet ”+” som används med hjälp av markören, fortsatt kallat ”Auto-fill”) är att behålla summaområde (D5:D40) och villkorsområde (C5:C40) samt villkoret ("ABC") intakt, men istället automatiskt förändra/flytta månad/flik, d.v.s. från ”Jan”, vidare till ”Feb”, ”Mar”, o.s.v. fram till och med ”Dec” för varje ny kolumn på raden i fliken där formeln används (kolumn C = Jan, kolumn D = Feb, etc.).

 

Detta är inte möjligt i default-läge för Auto-fill (som stegvis kommer att förändra summa- och villkorsområdet med ett kolumnsteg i taget (från D resp. C till E resp. D, osv. fram till N resp. M) men inte alls påverka flik (månad).

 

Om jag låser summaområde (D5:D40) och villkorsområde (C5:C40) med:

=SUMMA.OMF(Jan!$D$5:$D$40;Jan!$C$5:$C$40;"ABC")

så låser jag ju hela formeln, d.v.s. även månaderna/flikarna ”Jan” – ”Dec” (i den mån de alls hade gått att påverka flikarna/månaderna).

 

Finn det något sätt att lösa detta på – d.v.s. så att enbart månaderna förändras men resten av formeln behålls intakt?

[MH_]

Nej. Men du kan klistra ihop adressen så att den ändras när du byter kolumn, Typ:

=TEXT(EDATUM(1;KOLUMN(A1)-1);"MMM")&"!$D$5:$D$40"

=SUMMA.OMF(INDIREKT(TEXT(EDATUM(1;KOLUMN(A1)-1);"MMM")&"!$D$5:$D$40");INDIREKT(TEXT(EDATUM(1;KOLUMN(A1)-1);"MMM")&"!$C$5:$C$40");"ABC")

Eller, enklare, genom att skriva in fliknamnen någonstans.

[B.O.]

14 timmar sedan, MH_ sade:

Nej. Men du kan klistra ihop adressen så att den ändras när du byter kolumn, Typ:

=TEXT(EDATUM(1;KOLUMN(A1)-1);"MMM")&"!$D$5:$D$40"

=SUMMA.OMF(INDIREKT(TEXT(EDATUM(1;KOLUMN(A1)-1);"MMM")&"!$D$5:$D$40");INDIREKT(TEXT(EDATUM(1;KOLUMN(A1)-1);"MMM")&"!$C$5:$C$40");"ABC")

Eller, enklare, genom att skriva in fliknamnen någonstans.

Hej MH_!

Stort tack för svar

Jag testade som jag trodde (gissade) att det skulle fungera men är nog lite för mycket nybörjare för att helt få det att fungera utan lite mer detaljer i tillvägagångssättet.
D.v.s. jag fick det inte att fungera som tänkt.
Eller så var jag inte tydlig i min fråga och fick svar på något annat.

Låt mig därför förtydliga:

Det värde jag vill åt finns alltså i separata flikar (Jan, Feb, Mar, osv. fram till och med Dec)  i arbetsbladet.

 

Den flik, vi kan kalla den ”Sammanräkningsfliken”, där jag vill att värdena från respektive flik (Jan – Dec) skall presenteras är alltså den där jag använder summaformeln.
Högst upp i denna flik, på översta raden, har jag märkt upp alla månader Jan, Feb, etc., från C1 t.o.m. N1.
Cellerna A1 och B1 är rubriker för det som följer under dessa celler i sina resp. kolumner (namn (kolumn A] och beteckningar [kolumn B] för ett antal kategorier, samma kategorier som sedan är tänkt att visa sina respektive värden under raden för respektive månad [värden som alltså skall hämtas från de olika månadsflikarna, Jan – Dec]).

 

När jag använder formeln du lade upp här (anpassad till min rad med månader högst upp) enligt: =SUMMA.OMF(INDIREKT(TEXT(EDATUM(1;KOLUMN(C1)-1);"MMM")&"!$D$5:$D$40");INDIREKT(TEXT(EDATUM(1;KOLUMN(C1)-1);"MMM")&"!$C$5:$C$40");"ABC") fungerar den utmärkt till att automatiskt flytta beräkningen (kopiera höger) med ett kolumnsteg i taget, d.v.s. från C t.o.m. N. Men den hämtar inte några värden från de flikar (Jan t.o.m. Dec) som var avsikten.
Tanken med mitt arbetsblad är alltså att i fliken där beräkningen skall göras för Jan - Dec (Sammanräkningsfliken) så skall värden från flikarna Jan – Dec fyllas på enl. formeln ovan.
Men så blev det alltså inte. 

Så antingen är formeln inte avsedd för det jag tänkt mig eller så har jag helt missat något.

 

Så, till att börja med, är formeln du bifogat ämnat till att hämta värden (inom de angivna områdena) från de separata flikar jag avser, eller är den avsedd endast för den flik där man skriver in summaformeln?

Om det avser att hämta värden från de separata flikarna, har jag alltså missat något och får återkomma med följdfrågor (men du får gärna utveckla hur det är tänkt att jag skall använda formeln mer i detalj för att hämta värden från respektive flik).

 

Tack på förhand!

[MH_]

Den här delen skulle ha fortsatt att peka på A1. 
TEXT(EDATUM(1;KOLUMN(A1)-1)
Det var bara ett sätt att skapa texten jan, feb osv. När du började från C1 så borde du ha fått resultaten för mars.

Men, eftersom du redan har månaderna (fliknamnen) i C1:N1 så är det enklare att använda det istället. 

=SUMMA.OMF(INDIREKT(C1&"!$D$5:$D$40");INDIREKT(C1&"!$C$5:$C$40");"ABC")

Om din formel:

=SUMMA.OMF(Jan!$D$5:$D$40;Jan!$C$5:$C$40;"ABC")

Funkar så bör även den jag skrev fungera.

Testa de olika delarna. Ger det här en text som ser ut som adressen till flik "Jan", dvs: Jan!$D$5:$D$40  ?

=C1&"!$D$5:$D$40"

 

Och, om du har en modern excel, ger det här 35 rader med data från januarifliken?

=INDIREKT(C1&"!$D$5:$D$40")

Om du har en gammal excel måste du eventuellt mata in det som matrisformel (CTRL+SKIFT+RETUR när du står i cellen med formeln).

Ähh, testa och återkom om det inte funkar.

 

[B.O.]

15 timmar sedan, MH_ sade:

Den här delen skulle ha fortsatt att peka på A1. 
TEXT(EDATUM(1;KOLUMN(A1)-1)
Det var bara ett sätt att skapa texten jan, feb osv. När du började från C1 så borde du ha fått resultaten för mars.

Men, eftersom du redan har månaderna (fliknamnen) i C1:N1 så är det enklare att använda det istället. 

=SUMMA.OMF(INDIREKT(C1&"!$D$5:$D$40");INDIREKT(C1&"!$C$5:$C$40");"ABC")

Om din formel:

=SUMMA.OMF(Jan!$D$5:$D$40;Jan!$C$5:$C$40;"ABC")

Funkar så bör även den jag skrev fungera.

Testa de olika delarna. Ger det här en text som ser ut som adressen till flik "Jan", dvs: Jan!$D$5:$D$40  ?

=C1&"!$D$5:$D$40"

 

Och, om du har en modern excel, ger det här 35 rader med data från januarifliken?

=INDIREKT(C1&"!$D$5:$D$40")

Om du har en gammal excel måste du eventuellt mata in det som matrisformel (CTRL+SKIFT+RETUR när du står i cellen med formeln).

Ähh, testa och återkom om det inte funkar.

Hej igen och tack för ditt tålamod

Jag använder Excel 2019.

Den här gången fungerade det med formeln:

=SUMMA.OMF(INDIREKT(C1&"!$D$5:$D$40");INDIREKT(C1&"!$C$5:$C$40");"ABC")

Man kan även använda:

=SUMMA.OMF(INDIREKT(SAMMAN(C1;"!$D$5:$D$40"));INDIREKT(SAMMAN(C1;"!$C$5:$C$40"));"ABC")

Vilket ger samma resultat. Men ju enklare ju bättre så ditt förslag får väl koras till vinnare

Glad och tacksam för det engagemang som du och andra lägger ner här

[B.O.]

Fast, samtidigt, som så ofta, så händer det saker i detaljerna…

Det finns, i ett visst hänseende, ett problem med den här INDIREKT-formeln:
Om jag vill utöka (eller minska) summa- och villkorsområdena i någon av flikarna (d.v.s. lägga till fler, eller ta bort, rader i Excelbladet), vilket alltid kommer att vara fallet för varje flik, så uppdateras inte formeln med den förändringen.
Inte heller om jag tar bort ”låsningen”, t.ex.

=SUMMA.OMF(INDIREKT(C1&"!D5:D40");INDIREKT(C1&"!C5:C40");"ABC")

Använder jag däremot den ursprungliga formeln:

=SUMMA.OMF(Jan!$D$5:$D$40;Jan!$C$5:$C$40;"ABC")

så har Excel inga problem att hänga med (uppdatera formeln) när jag utökar respektive minskar summa- och villkorsområdena (lägger till eller drar ifrån rader i Excelbladet).

Så länge jag inte kan använda INDIREKT-formeln ovan så att det uppdaterar ökningar och minskningar av antalet rader i Excelbladen blir det tyvärr inte möjligt att använda en sådan formel och jag blir tvungen att ta jobbet med att använda den ursprungliga formeln.
Skulle det, trots allt, finnas en lösning även på den delen så vore det såklart toppen 😊

[MH_]

Med den här metoden går det inte. Eftersom..

"!$D$5:$D$40"

..är en textsträng så finns det ingen som helst koppling till det som händer på databladen

Om du inte har mer info på bladet (nedanför D40)så är det enklaste att helt enkelt låta formeln peka på ett större område, typ "!$D$5:$D$4000" och hoppas att du aldrig får mer än 3995 rader...

eller ta hela Kolumnerna (eftersom det måste stå "ABC" i villkorskolumnen så är ju risken för att du tar med rader som inte skall vare med väldigt lite)

Dvs motsvarigheten till

=SUMMA.OMF(jan!$D:$D;jan!$C:$C;"ABC")

Det blir något i stil med:

=SUMMA.OMF(INDIREKT(C$1&"!$D:$D");INDIREKT(C$1&"!$C:$C");"ABC")

Normalt sett brukar man undvika att ha för stora områden i formler. Men excel har blivit väldigt bra på att begränsa området själv så det borde inte bli "segt".

Om du inte kan använda ett stort område så måste du köra mycket krångligare varianter (om det överhuvudtaget går) för att få det dynamiskt. Testa om Helkolumn, eller för många rader, funkar för dig innan vi krånglar till det i onödan.

[B.O.]

Hej igen!

Du har ju såklart helt rätt:

19 timmar sedan, MH_ sade:

Med den här metoden går det inte. Eftersom..

"!$D$5:$D$40"

..är en textsträng så finns det ingen som helst koppling till det som händer på databladen

Men det hade jag, innan du upplyste mig om det, inte en aning om, främst beroende på att jag inte, på ett mer fundamentalt plan, känner till Excelbegreppet "textsträng" och vilka möjligheter och begränsningar som följder med det. Det är ju just sådant som skiljer dem som verkligen vet och kan från glada amatörer som jag själv.

Tanken att använda "oändlig" förlängning av kolumnerna hade dock slaget mig - men hur man åstadkom detta på ett enkelt vis visste jag inte heller.

Tack vara hjälpen har vet jag nu i alla fall lite mer (och hur mycket jag ännu inte vet...).

Och som du också helt riktigt påpekar:

19 timmar sedan, MH_ sade:

eftersom det måste stå "ABC" i villkorskolumnen så är ju risken för att du tar med rader som inte skall vare med väldigt lite

så är upplägget att definiera de respektive hela kolumnerna som beräkningsområden det rätta.
Och det är också så jag kommer att använda formeln (som jag testat och kunnat konstatera att den fungerar precis som tänkt):

20 timmar sedan, MH_ sade:

=SUMMA.OMF(INDIREKT(C$1&"!$D:$D");INDIREKT(C$1&"!$C:$C");"ABC")

-  med några få, sex för att vara exakt, undantag:

Sex kategorier (med sina respektive förkortningar i villkorskolumnen) återkommer varje månad, år från år, men med viss årsvis förändring beträffande summan.

För att spara tid har jag därför dessa, redan från början, inskrivna i arbetsbladets utgångsversion (färdiga utgångsversioner finns förberedda för minst ett år i förväg), längst ner, under den del som ingår i det som räknas samman inom det givna beräkningsområdet- varför dessa, i förhand inskrivna, värden också, oönskat, kommer att räknas in summan med formeln ovan (som ju utan begränsning tar med allt inom kolumnerna) - men som inte är något problem eftersom det enkelt justeras genom att för just dessa kategorier använda standardformeln - det är ju trots allt bara sex kategorier, så det är ju snabbt ordnat.

På detta sätt förenar jag nu nyttan med formeln INDIREKT och de från början inskrivna sex kategorierna. (Vid varje nytt år justerar jag dessa sex kategorier med den prisförändring som eventuellt har skett, vilket sällan brukar vara mer ett ett par, tre stycken. När det sedan under det aktiva året närmar sig respektive förfallodatum för något av dessa kategorier är det enkelt att kopiera värdena för dessa (tre värden: namn, kontoförkortning och summa) på raden för rätt datum.)

För alla andra kategorier blir det nu alltså enkelt att skapa ett upplägg med INDIREKT som, oavsett justeringar av rader, etc., kommer att fungera felfritt för varje flik i arbetsbladet. Sedan, när allt är genomgånget och kontrollerat, är det bara att kopiera alltsammans till kommande års arbetsblad - och med tillförsikt se kommande årsredovisningar an...

Tack för all hjälp!

[MH_]

Om du har en kolumn med datum så kan du ju lägga in ett villkor för det också (SUMMA.OMF tillåter ju flera villkor) och bar räkna sånt som händer före dagens datum.

Eller inte. Krångla inte till det om du är nöjd.

[B.O.]

On 2023-01-23 at 11:14, MH_ sade:

Om du har en kolumn med datum så kan du ju lägga in ett villkor för det också (SUMMA.OMF tillåter ju flera villkor) och bar räkna sånt som händer före dagens datum.

Eller inte. Krångla inte till det om du är nöjd.

Hej igen!

Fick precis en notifiering (e-post) om att nytt svar kommit i tråden (verkar som om det är ganska lång fördröjning med e-postutskicken, först trodde jag inte alls den tjänsten fungerade [trots att jag har den förvald], men som sagt, efter en vecka så...)

Hursomhelst, har du ju såklart rätt i det du säger. Både vad SUMMA.OMF beträffar och, inte minst, ifråga om att inte krångla till det , och apropå just det:

Efter en del testande har jag nu gjort om upplägget så att jag kan använda INDIREKT utan några inskränkningar (fördelarna väger helt enkelt över när man vill få det så enkelt som möjligt).

Tack igen för engagemanget!

[Cecilia]

14 minuter sedan, B.O. sade:

Fick precis en notifiering (e-post) om att nytt svar kommit i tråden (verkar som om det är ganska lång fördröjning med e-postutskicken, först trodde jag inte alls den tjänsten fungerade [trots att jag har den förvald], men som sagt, efter en vecka så...)

Det går att välja mejl med olika intervall från omedelbart till en gång i veckan. Ta bort dig som följare och så lägg till dig igen så kan du välja hur du vill ha det.

[B.O.]

2 timmar sedan, Cecilia sade:

Det går att välja mejl med olika intervall från omedelbart till en gång i veckan. Ta bort dig som följare och så lägg till dig igen så kan du välja hur du vill ha det.

Hej!

Min inställning var ställd på:

"En notifiering när nytt innehåll postas"

Men när jag gick in och tittade på mina Generella inställningar (mina kontoinställningar) visade det sig att jag missat på ett ställe.

Till att börja med hade jag inte, i tillräcklig utsträckning, insett att "notifiering" och "e-post" är två skilda begrepp.

Nu har jag hursomhelst justerat och uppdaterat inställningarna för både notifieringar och e-post.

Tack, Cecilia, för att du gjorde mig uppmärksam på detta!