• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

sommen.als verwijzingen bij gesloten werkmap

Status
Niet open voor verdere reacties.
J

jowey

Gebruiker
Lid geworden
18 mei 2017
Berichten
98
Hallo,

voor een factuur wil ik in een ander bestand voor producten met emballage automatisch laten tellen hoeveel emballage klant afneemt.
Het gaat om een totaal van 15 producten die in emballage zitten.

Nu had ik deze formule die werkte;

Code: 
=SOM(SOMMEN.ALS('[klant.xlsx]Blad1'!$G$12:$G$30;'[klant.xlsx]Blad1'!$A$12:$A$30;{"product c"\"product d"\"product g"}))/10

Probleem is echter deze formule werkt alleen als het doelbestand openstaat.
Hoe los ik dit op. Heb gelezen met matrixformules maar het lukt mij niet om die omgeschreven te krijgen

PS
Het liefst zou ik de aantallen van kolom b t/m f optellen aangezien in kolom g alles keer 10 of keer 11 of keer 13 of helemaal geen vermenigvuldiging plaatsvind.
Nu kan ik product e en f niet meenemen in de formule en moet ik dezelfde formule schrijven maar dan delen door 11 of 13

gr Jowey

Bekijk bijlage test.factuur.xlsx
 
Volgens mij kun je eenvoudigweg uit de kolommen B t/m F halen hoeveel emballage klant afneemt.
 
omdat alle facturen in een eigen excel bestand staan.

mappenstructuur:
week 22
klant 1.xlsx
klant 2.xlsx
klant 3 xlsx
etc
etc

en zo is er voor iedere week een andere map met ongeveer 40 klanten en dus 40 excelbestanden
 
die gaat helaas gewoon met de hand.
Aan de hand van pakbonnen van chauffeurs
 
Okay, dan maak je je het ongelooflijk moeilijk dus. Je kunt met een tabel werken en 40 klanten per week in 1 tabel zetten en dat dan per factuur filteren.
 
Met de som van B12:F25 heb je alles toch? Zie ook #2. Zelf zou ik vanuit 1 gestructureerd bestand werken en dan het eea met een macro oplossen.

Even zonder foutafhandeling:
Code: 
Sub VenA()
  c00 = "E:\Temp\"
  With Sheets("Pakbon")
    Sheets("database").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 3) = Array(.[B6], .[A8], Application.Sum(.Range("B12:F25")))
    .Copy
    With ActiveWorkbook
      .Sheets(1).UsedRange.Cells = .Sheets(1).UsedRange.Cells.Value
      .SaveAs c00 & [A8] & [B6], 51
      .Close 0
    End With
    .Range("A8,B6:E6,B12:F25").ClearContents
  End With
End Sub
 

Bijlagen

  • test.doelbestand.xlsb
    23,3 KB · Weergaven: 33
VenA

ik begrijp dan niet wat jij bedoelt en ook niet uit #2

bij sommen.als kun je toch geen matrix als optelbereik gebruiken?

Ik heb in kolom A 13 verschillende criteria waaraan voldaan moet worden om in matrix B12:F35 op te tellen.
Hoe schrijf ik dat dan in excel? sommen.als kan iig niet

Ik heb online iets gevonden over somproduct maar dat voorbeeld was voor 1 criteria.
Ik heb er nu sws 1 compleet bestand van gemaakt

Hieronder voorbeeld van compleet bestand met deze werkende formule. Hoe versimpel ik dit dan met een matrix B12:F35??

Code: 
=SOM(SOMMEN.ALS('klant 1'!G12:G35;'klant 1'!A12:A35;{"product 1"\"product 2"\"product 3"\"product 4"\"product 5"\"product 6"\"product 7"\"product 8"\"product 9"\"product 10"}))+(SOMMEN.ALS('klant 1'!G12:G35;'klant 1'!A12:A35;"product 11")/11)+(SOMMEN.ALS('klant 1'!G12:G35;'klant 1'!A12:A35;"product 12")/13)+(SOMMEN.ALS('klant 1'!G12:G35;'klant 1'!A12:A35;"product 13")/17)


Bekijk bijlage werkend.voorbeeld.xlsx
 
Moet de uitkomst niet 325,5 zijn? Of tellen de producten vanaf product 14 niet mee. In je OP deel je een aantal producten door 10 nu ineens niet meer. Anders zou SOM() prima werken. Van mijn voorstel om gestructureerd te werken is niets terug te vinden.

Gestructureerd werken:
- Werk vanuit 1 bestand;
- Leg je constanten en variabelen vast in tabellen;
- Gebruik deze gegevens in de formules en gegevensvalidatie;
- Handel het opslaan van de totalen af met een macro;
- Sla het tabblad op met een macro.

Code: 
Sub VenA()
Dim j As Long, jj As Long, t As Long, c00 As String, ar, ar1
  c00 = "E:\Temp\"
  ar = Sheets("Basis").ListObjects(3).DataBodyRange
  With Sheets("Pakbon")
    ar1 = .Cells(11, 1).CurrentRegion
    For j = 2 To UBound(ar1)
      If ar1(j, 1) <> "" Then
        For jj = 1 To UBound(ar)
          If ar(jj, 1) = ar1(j, 1) And ar(jj, 2) = "Ja" Then
            t = t + ar1(j, 7) / ar(jj, 3)
            Exit For
          End If
        Next jj
      End If
    Next j
    Sheets("Database").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 3) = Array(.[B6], .[A8], t)
    .Copy
    With ActiveWorkbook
      .Sheets(1).UsedRange.Cells = .Sheets(1).UsedRange.Cells.Value
      .SaveAs c00 & [A8] & [B6], 51
      .Close 0
    End With
    On Error Resume Next
    .Columns(2).Resize(, 5).SpecialCells(2, 1).ClearContents
  End With
End Sub
 

Bijlagen

  • test.doelbestand (1).xlsb
    26 KB · Weergaven: 24
beste VenA

allereerst bedankt voor je voorbeeldbestand. Ziet er strak uit.
Wat betreft je opmerking over delen door 10, dat klopt idd. Vergeten.
Omdat ik de producten genummerd heb lijkt het willekeurig maar ik weet bij welk product welke factor hoort en of er emballage bij hoort.
Zoals jij dat gedaan hebt bij basis is dat erg handig.



Vragen aan jou;

Het liefst zou ik in een bestand werken zoals die van jou, maar ik moet nog uitdokteren hoe je die hebt opgesteld.
Waar vul ik een nieuwe factuur in? Als ik nu bijvoorbeeld de factuur van week 23 wil inzetten voor klant 1. Hoe doe ik dat dan?
En hoe haal ik die factuur dan omhoog als ik die wil zien zoals bij het tabblad pakbon. Of is dit document daar niet geschikt voor en alleen puur voor analyse.
Of vraag ik nu teveel van je?

Alvast bedankt.

gr jowey

toevoeging;
ik zie dat je voor de prijs bijvoorbeeld een formule gebruikt om die op te zoeken aan de hand van het product.
Erg handig alleen dan zou ik die moeten uitbreiden.
Niet iedere klant betaald namelijk hetzelfde voor een product.
 
Laatst bewerkt:
Je haalt zoveel zaken door elkaar waardoor het onmogelijk wordt om op welke vraag dan ook een antwoord te geven. Probeer voor jezelf te verzinnen wat je nu echt wil. Een pakbon is iets compleet anders dan een factuur.
 
Er staat pakbon maar het is gewoon een factuur.

Op dit moment heb ik 1 Excel bestand met op elk tabblad een andere klant.

Daarnaast heb ik een tabblad waar alle bedragen uit de facturen gebundeld zijn zodat je snel kunt zien wie wat moet betalen.

Als laatste 1 tabblad waarop ik zoals in mijn voorbeeld de emballage per klant bereken

Voor elke week heb ik 1 zo'n bestand.

Wat ik wil bereiken:

Hetzelfde als hierboven maar dan 1 bestand voor een heel jaar. Maar dan overzichtelijker. Bovenstaande heeft nu ruim 40 tabbladen.

een tabblad met een invulformulier/opvraagformulier in de layout van de factuur om een database te creeeren. Als je dan bijvoorbeeld klant 1 selecteert zou hij dan automatisch de prijzen moeten laden die voor die klant gelden.

Een tabblad met de prijzen per klant aangezien deze verschillen en de markt iedere week schommelt en ik deze dus moet kunnen aanpassen.

De lay-out van de factuur met de dagen van de week moet zo blijven omdat de klant wil kunnen controleren of de factuur klopt. Ze krijgen pakbonnen per dag.

Wat ik vraag....
Is dit mogelijk En zo ja kan je mij op weg helpen? Ik ben niet super met Excel maar vind het leuk om de moeite te nemen om dingen zelf uit te vinden. Alleen ik merk wel dat Excel een bos is en sturing noodzakelijk is.

Ik hoop dat ik een beetje duidelijk ben geweest.

Gr jowey
 
Laatst bewerkt:
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan