Hjälp med Excel makro - matplanering

[a-son]

Hej, ramlade på den här excel filen,

http://office.microsoft.com/en-us/templates/monthly-family-meal-planner-TC103987059.aspx

 

Funkar bra men är en bugg i den, vet inte hur man ska kontakta dom för att räta till det eller om jag gör något fel.

Den funkar som så att under Meal plan så skriver man in vad man ska äta, och under fliken Shopping så är det ett makro som generar en lista.

 

om man nu ändrar antalet "Serves" under meal plan, och sedan kör makrot, så tar den inte och räknar om antalet som ska handlas,

men ändrar man datumet längre fram där inget är planerat så töms listan iallafall, så något är tok där,

 

jag är grön på det här med makro så lite tips vore bra.

 

Meal_Planner_org.zip

[Monshi]

Jadu, den där saknade en del och det var ett litet detektivarbete att finna vad.

 

Två saker har jag ändrat

1: Formeln Multiplier. Men den har en brist än, du kan bara servera en viss rätt en gång i veckan. Eller rättare sagt den mängd du anger för första serveringen kommer gälla även för andra. Går att lösa men kolla först att övriga blir rätt. Formeln blir lätt lite..komplex.

 

2: Pivottabellen. Lagt in ett beräknat fält som saknades, ersatte Quantity med detta.

 

Inte helt säker på att allt fungerar som tänkt nu heller, du får testa.

 

Meal_Planner_org1.zip

[a-son]

Tackar! funkar mycket bättre nu,

Men som du säger, den för spel om man serverar samma sak 2 gånger.

Och det är inte att den tar det som serveras första gången, utan blir något helt annat.

 

Under recipes fältet Recipes link, vore ju bra att få med det i mealplan egentligen,

för när man skriver ut den så får man med vart man ska leta efter receptet.

[Monshi]

Länk inget problem.

 

Att kunna ha samma rätt två gånger... nej vänta den lösning jag gjorde, inget bra.

 

Multiplikationen ska inte ske i Pivot, den får ske på bladet innan pivot. Pivot ska bara summera så ska det fungera lite bättre... ahh, okej. Testa denna

 

Tre saker ändrade

1: Lagt till en beräkningskolumn på bladet som ska vara dolt som där räknar ut för varje rad/varje ingrediens

2: Ändrat Multiplier-formeln till att räkna medelantalportioner av en rätt, dela det med receptets antal portioner

3: Ändrat Pivottabellens fält till att inkludera det nya i steg 1 ovan.

 

Samt lagt in en kolumn med länk till receptet om sådan finns. Radera inte denna när du raderar matplanen.

 

Mmh, fast allt detta måste gå att göra på enklare vis. Multiplicieringsformlen börjar bli lång:

=(OMFEL(MEDEL.OM(FÖRSKJUTNING(WeekPlan;;2;1000);calculations!AF9;FÖRSKJUTNING(WeekPlan;;1;1000));0)+OMFEL(MEDEL.OM(FÖRSKJUTNING(WeekPlan;;4;1000);calculations!AF9;FÖRSKJUTNING(WeekPlan;;1;1000));0)+OMFEL(MEDEL.OM(FÖRSKJUTNING(WeekPlan;;6;1000);calculations!AF9;FÖRSKJUTNING(WeekPlan;;1;1000));0))/INDEX(FÖRSKJUTNING(WeekDishes;;2;1000);PASSA(calculations!AF9;FÖRSKJUTNING(WeekDishes;;1;1000);0))

 

max 1000 rätter och 1000 måltider...

 

 

Meal_Planner_org1.zip

[a-son]

Hej, funkar bra med beräkningarna nu, tackar!

och det där med länken funkar bra med, 

hade egentligen tänkt kanske om det går att få en rad under maträtten

med länken, eller om man skriver typ så här som referens 

"kokbok bl. 501"

så ska det synas, men då kanske den blir för hög för att bli bra vid utskrift.

 

 

om man ska försöka sej på att förstå hur det här fungerar, 

vart ska jag börja, är det makrot som gör allt eller är det andra ställen?

vart kan man läsa för att förstå makrot?

[Monshi]

Lägga under en post blir inte lätt, det går inte att automatisera skapandet av länkarna på samma sätt.  Däremot kan man lösa på annat vis, flytta på länkarna i tabellen eller kanske skapa en skuggtabell där du länkar. Dock, krävs ändring i koden om de ska flyttas!

 

Förstå hur det fungerar? Jag kan beskriva lite kort grunden så kan vi se om du förstår.

1: Du har tre listor, recept, namn på rätter och din planering.

2: Första steget i analysen är att filtrera ut angiven periods recept. Sker med VBA-kod, avancerat filter och skickas till cell med namnet WeekPlan på bladet Calculations.

3: En lista byggs upp i VBA över antal portioner och rätter

4: Områden rensas.

5:Via sökning/jämförelse i VBA mellan listan från 3 och listan med recept kopieras alla ingredienser som behövs till dolda bladet.

6: Två formler klistras in, ena har jag ändrat, andra har jag lagt till.

7: Pivottabellen på bladet Shopping uppdateras och ställs in, den baseras på listan som skapats i steg 6 och 7.

 

och ja, nu när jag gått igenom koden en gång till hittade jag ett fel så använd denna som jag bifogar istället.

 

Meal_Planner_org1.zip

[a-son]

tackar, har gjort ett par dagars matlista nu, funkar fint.

Ska titta närmare på koden senare,

 

Det där med recept länk, det kanske ska göras en ny flik med 2 columner som visar veckans alla recept och källa (länkar)

 

för att importera shoppinglistan till http://www.ourgroceries.com

så har jag testat att göra så här för att få rätt format,

Kopierat varorna från shopping fliken i meal_planner bladet till nya bifogat excel,

avrundat uppåt utan decimaler, och lagt till paranteser

så kolumn E går att importera till ourgroceries,

ska se till att det blir gjort direkt i meal_planner bladet.

ourgroceries.xlsx

[a-son]

Lägga under en post blir inte lätt, det går inte att automatisera skapandet av länkarna på samma sätt.  Däremot kan man lösa på annat vis, flytta på länkarna i tabellen eller kanske skapa en skuggtabell där du länkar. Dock, krävs ändring i koden om de ska flyttas!

 

Förstå hur det fungerar? Jag kan beskriva lite kort grunden så kan vi se om du förstår.

1: Du har tre listor, recept, namn på rätter och din planering.

2: Första steget i analysen är att filtrera ut angiven periods recept. Sker med VBA-kod, avancerat filter och skickas till cell med namnet WeekPlan på bladet Calculations.

3: En lista byggs upp i VBA över antal portioner och rätter

4: Områden rensas.

5:Via sökning/jämförelse i VBA mellan listan från 3 och listan med recept kopieras alla ingredienser som behövs till dolda bladet.

6: Två formler klistras in, ena har jag ändrat, andra har jag lagt till.

7: Pivottabellen på bladet Shopping uppdateras och ställs in, den baseras på listan som skapats i steg 6 och 7.

 

och ja, nu när jag gått igenom koden en gång till hittade jag ett fel så använd denna som jag bifogar istället.

 

Det är ett fel i uträkningen i den senaste,

Om man tar samma maträtt som main dish, så summerar den rätt,

men lägger även till samma rätt som en side order, så blir det någon fel med  uträkningen.

 

 

Har försökt tittat på koden men är nog inte med i matchen, får leta lite på nätet för att lära mej mer.

[Monshi]

Det har du rätt i. En ny uppdatering av den lååånga formeln.

 

och lagt till ett blad.

 

Koden/formlerna. Tja, ja, kanske inte helt lätt att sätta sig in i.

 

 

 

 

 

Meal_Planner_org1.zip

[a-son]

Snabba svar, Tackar!

Ja nu funkar det bättre,

ser att du lagt till flik för recept, där kanske man kan ha en bred kolumn och visa i klar text, och är det en webb adress så blir det en länk.

 

Jag har öppnat makrot och ser VBA koden,

men den formeln som du ang tidigare hittar jag inte,

vart finns den?

 

 

Har flera ideer som jag skulle vilja ha i den här...

[Monshi]

Låste det nya bladet, utan lösen, för att dölja lite felmeddelanden samt att du inte har något att ändra där.

Kolumnbredd kan du ändra själva, bara att låsa upp.

Formeln hittar du via Namnhanteraren, MultiplierFormula heter den.

=SUMMA(SUMMA.OM(FÖRSKJUTNING(WeekPlan;;2;8;1);calculations!B1;FÖRSKJUTNING(WeekPlan;;1;8;1));SUMMA.OM(FÖRSKJUTNING(WeekPlan;;3;8;1);calculations!B1;FÖRSKJUTNING(WeekPlan;;1;8;1));SUMMA.OM(FÖRSKJUTNING(WeekPlan;;4;8;1);calculations!B1;FÖRSKJUTNING(WeekPlan;;1;8;1)))/SUMMA(ANTAL.OM(FÖRSKJUTNING(WeekPlan;;2;8;1);calculations!B1);ANTAL.OM(FÖRSKJUTNING(WeekPlan;;3;8;1);calculations!B1);ANTAL.OM(FÖRSKJUTNING(WeekPlan;;4;8;1);calculations!B1))/INDEX(FÖRSKJUTNING(WeekDishes;;2;1000);PASSA(calculations!B1;FÖRSKJUTNING(WeekDishes;;1;1000);0))

så ser den ut nu. Kom inte på något enklare sätt att få till det.

 

 

har dolt bladet som ska vara dolt.

Men nu får du allt testa göra lite själv... :-)

[a-son]

Ja, nu när jag vet vart den är så blir det lättare att prova lite saker,

Där fanns det en massa namn definierade, används alla dom?

 

jag öppnade något makro som var knytet till knappen "Compile list" men det kanske är något annat det

 

 

Edit: börjar nog få lite hum om hur det hänger ihop med alla dom när namnen, för snoka runt lite mer imorgon

[Monshi]

Tja, namnen på det dolda bladet används av VBA för att veta var tabellerna som den ska läsa och skriva till finns, så att de hamnar på samma plats varje gång.

 

Det makrot du öppnade, det är det jag beskrivet ovan i princip.

[a-son]

så namn MultiplierFormula är formeln som du har redigerat,

och det anropas i på fliken calculations under Multiplier?

 

Ok, och dom andra används på andra ställen,

kan man ta reda på om alla används eller om dom ligger och skräpar?

 

Har testat att gjort lite ändringar:

1. under shopping, så har jag start och end datum,ändrade i VBA koden så man kan planera en hel månad men veckohandla.
2. under this week testade jag på översta raden att visa klar text för referens av källa

 

 

Meal_Planner_131030.zip

[Monshi]

Namn som inte används är inget generellt problem, bäst att inte röra dem. Men ja, det är den formeln som används.

 

Oj, du ändrade i VBA-koden? Måste kika :-)

Notera bara att i Multiplier har jag lagt in en gräns om åtta rader, dvs åtta dagar handel. Hade 1000 förut iofs, går med andra ord att utöka om du vill sätta upp lista för fler dagar.

 

Klartext/hyperlänk. Tyvärr autoformaterar Excel hela kolumnen till hyperlänk om det genereras en hyperlänk av formeln. Dvs antingen få du ha allt i klartext eller så blir all text hyperlänkar i den kolumnen på This Week. eller så delar vi på det i den rapporten, har text i en kolumn och eventuell länk i annan kolumn.

[a-son]

Låste det nya bladet, utan lösen, för att dölja lite felmeddelanden samt att du inte har något att ändra där.

Kolumnbredd kan du ändra själva, bara att låsa upp.

Formeln hittar du via Namnhanteraren, MultiplierFormula heter den.

=SUMMA(SUMMA.OM(FÖRSKJUTNING(WeekPlan;;2;8;1);calculations!B1;FÖRSKJUTNING(WeekPlan;;1;8;1));SUMMA.OM(FÖRSKJUTNING(WeekPlan;;3;8;1);calculations!B1;FÖRSKJUTNING(WeekPlan;;1;8;1));SUMMA.OM(FÖRSKJUTNING(WeekPlan;;4;8;1);calculations!B1;FÖRSKJUTNING(WeekPlan;;1;8;1)))/SUMMA(ANTAL.OM(FÖRSKJUTNING(WeekPlan;;2;8;1);calculations!B1);ANTAL.OM(FÖRSKJUTNING(WeekPlan;;3;8;1);calculations!B1);ANTAL.OM(FÖRSKJUTNING(WeekPlan;;4;8;1);calculations!B1))/INDEX(FÖRSKJUTNING(WeekDishes;;2;1000);PASSA(calculations!B1;FÖRSKJUTNING(WeekDishes;;1;1000);0))

 

Med min modifiering så går det att skapa chopping lista om samma mat finns med i dom 7 första dagarna,

men lägger jag till ny rätt längre ner så blir det fel, måste ha med din 8 raders gräns,

 

har läst hjälpen för FÖRSKJUTNING men förstår inte,

 

FÖRSKJUTNING(ref; rader; kolumner; [höjd]]; [bredd])

där står det att ref; rader; kolumner; är obligatoriska

 

och i din kod står det:

FÖRSKJUTNING(WeekPlan;;2;8;1)

 

stämmer det då följande:

ref=WeekPlan

och då blir väl rader tom?

kolumner=2

höjd=8

bredd=1

har jag fattat rätt? och såna fall hur kan det vara tomt på rader när det är obligatoriskt?

 

hur många rader med ingredienser kan man ha?

 

ändrade iallfall höjden, så funkar det som tänkt verkar det som,

Ny release med lite andra mat rätter

Meal_Planner_131030_natt.zip

[Monshi]

Man kan skriva

FÖRSKJUTNING(WeekPlan;;2;8;1)

eller

FÖRSKJUTNING(WeekPlan;0;2;8;1)

det är egalt.

 

Tabellen med ingredienser, den är dynamisk. Den kan vara hur lång somhelst. Inga problem där. Det är bara i de referenser, de formler, som är relativa som Multiplier som man kan bygga in begränsningar i.

Med inte alltför stort ingrepp kan man göra även denna dynamisk.

Bara att räkna antalet dagar som ska beräknas så blir den helt dynamisk.

 

Ett litet tips, på Shopping-sidan

I en kolumn till höger, exempelvis O, skriv i en cell (rad 2)

=IDAG()

I kolumnen bredvid, skrive

=ShoppingStartDate

och på raden under

=O2+1

resp

=P2+1

och kopiera nedåt.

Formatera med samma format som övriga datumceller.

Använd sedan dessa två tabeller som grund för dataverifieringslistor där du väljer datum, den första för startdatum, den andra för slutdatum.

Kolumnerna med datumlistorna kan du dölja.

[a-son]

Ett litet tips, på Shopping-sidan

I en kolumn till höger, exempelvis O, skriv i en cell (rad 2)

=IDAG()

I kolumnen bredvid, skrive

=ShoppingStartDate

och på raden under

=O2+1

resp

=P2+1

och kopiera nedåt.

Formatera med samma format som övriga datumceller.

Använd sedan dessa två tabeller som grund för dataverifieringslistor där du väljer datum, den första för startdatum, den andra för slutdatum.

Kolumnerna med datumlistorna kan du dölja.

 

Har gjort så nu, men är inte med i matchen alls,

varför jag ska göra det och hur jag ska göra dom där dataverifieringslistorna?

 

Är det för att kontrollera så man inte skriver ett start date som är tidigare än idag?

[Monshi]

För att göra det lite lättare att mata in datum helt enkelt.

 

markera cellen för startdatum, ta upp dataverifieringsverktyget. Välj lista och ange din datumtabell som källa.

Och samma för slutdatum med andra tabellen.

[a-son]

Har gjort så nu, får varning om man inte är mellan datum.

tänkte om det kanske är bättre att få en dropplista med datum som hämtas från meal_plan

[Monshi]

? Det ska fungera precis som om du skriver in datum förhand om du gjort rätt.

Datumen där är helt fristående från din plan, om du inget planerat inom intervall hämtas inga data helt enkelt.

[a-son]

missade att göra det till lista,

funkar nu,

nu går det att välja datum mellan idag och framåt,

 

Sedan sitter jag och testar lite att på sidan om

vill ha dom här 2 cellerna hopslagna till en och avrundat

fast potatis 1,5

 

=AVRUNDA.UPPÅT(FÖRSKJUTNING(C1;0;-1;1;1);0)

ger iallfall en avrundning

men vill även ha det här före, så det blir i samma cell

=A1&" "

 

 

tanken är att sedan få in det i pivottabellen för enkelt exportera det till shopping appen jag använder 

Meal_Planner_131031_fm.zip

[Monshi]

Var tänkte du dig ha denna avrundning?

 

En tabell vid sidan om tabellen på bladet Shopping? Eller kanske en spegling på annat blad, ett enkelt blad för export?

 

Exempelvis på ett annat blad skriva in formlerna

=OM(shopping!B6="";"";shopping!B6)

=OM(shopping!D6="";"";AVRUNDA.UPPÅT(shopping!D6;0))

=OM(shopping!C6="";"";shopping!C6)

i en ny tabell i den ordningen. En tabell med tillräckligt med rader för att ta hand om hela Pivotresultatet på Shoppingbladet.

 

Eller om du vill ha allt i en cell går det givetvis bra

=OM(shopping!B6="";"";shopping!B6 & " " &AVRUNDA.UPPÅT(shopping!D6;0))

[a-son]

Lyckat med något!

hummerfond	1

=A35&" "&"("&AVRUNDA.UPPÅT(FÖRSKJUTNING(C35;0;-1;1;1);0)&")"

hummerfond (1)

[a-son]

gjorde en ny flik för export och ändrade till

=OM(shopping!B6="";"";shopping!B6 & " " &"(" &AVRUNDA.UPPÅT(shopping!D6;0)& ")")

 

blir som jag vill nu,

fattas bra att få till en export knapp till text fil