Index, vergelijken en als formule koppelen in Excel

[Anoniem: 1059941]

Hallo allemaal,

Ik loop vast met mijn formule in Excel. Ik heb in één excel folder met meerdere tabbladen. Zo heet een tab verkoopregister. Hierin staan facturen met regelnummers en producten:
Hieronder een voorbeeld:

Factuur Regel Product
2018001 1 Product 1
2018001 2 Product 2
2018001 3 Product 3
2018002 1 Product 2

Hiernaast staat er nog andere informatie betreffende de producten maar dat is voor deze vraag niet relevant.
Nu wou ik graag in het tabblad waar een factuur wordt gegenereerd, een formule zetten waardoor met het veranderen van het factuurnummer de producten automatisch worden ingevuld.

Ik had deze formule voor factuurregel 1:

=INDEX(Productkolom;VERGELIJKEN(Factuurnummer;(ALS(Regelkolom=1;Factuurnummerkolom);0))

Maar dit lijkt niet te werken , kan iemand mij helpen?

Alvast bedankt

[ Voor 49% gewijzigd door Anoniem: 1059941 op 05-04-2018 13:05 . Reden: Nog niet af ]

[Reptile209]

Ik snap er geen hout van hoe je sheet nu in elkaar zit. En vertel eens wat meer over "lijkt niet te werken": krijg je geen resultaten, foutcodes, verkeerde (nouja, onverwachte) resultaten?

Gelukkig zie ik in ieder geval al wat dingen die mis zijn aan je formule, misschien kom je daar al verder mee.
* je formule is niet compleet, ik mis tenminste 1 haakje sluiten (copy-paste foutje?).
* VERGELIJKEN() heeft 3 parameters nodig: de waarde om te vinden (Factuurnummer, ok), het cellenbereik waarbinnen moet worden gezocht (staat bij jou een halve ALS(), en die geeft 1 enkele waarde terug, dat gaat niet werken) en een match-type (0, dus exacte match. Dat klopt wel).
* Wat probeer je met die incomplete (want er mist een waarde voor wanneer ALS onwaar is) ALS() te bereiken? Dat alleen regelnummer 1 van de betreffende factuur gevonden wordt? Kijk dan eens naar bijvoorbeeld deze beschrijving voor INDEX/MATCH (INDEX/VERGELIJKEN) met meerdere criteria.

Overigens wordt in die link wel aangegeven dat voorkennis van INDEX/MATCH nodig is. Zorg dus dat je daar eerst wat vertrouwder mee raakt. Te beginnen met de help van Excel.

[Itrme]

@Reptile209 heeft een punt, zonder een concreet voorbeeld wordt het een beetje gissen hoe je excel document in elkaar steekt.....

@Anoniem: 1059941 Voor zover ik het begrijp heb je een excel bestand, met meerdere sheets. Elke sheet heeft netjes zijn eigen naam namelijk het factuurnr. en vervolgens wil jij een extra sheet hebben waarin je het factuurnummer invult zodat er uitrolt wat jij wil uitprinten?

Je kan een sheet opzetten met de functie =INDIRECT(SHEETNAAM&"!??")

Sheetnaam, laat je dan verwijzen naar de cel waar je je factuurnummer in gaat vullen. Laten we zeggen. die staat in A1 van je weergave/print sheet -> =INDIRECT(A1&"!??")

Dan de cel kiezen vanuit waar je de waarde wil ophalen: laten we zeggen, we beginnen met bijv de naam van de gefactureerde, die staat misschien wel in cel B3 -> =INDIRECT(A1&"!B3")

Nadeel van deze formule, als je deze gaat slepen verandert de eerste verwijzing wel, de 2e niet, terwijl je het liever andersom wil. Bij een ontbrekende waarde krijg je 0, wat misschien niet wenselijk is als je een hele korte lijst van producten hebt. Oplossing zou misschien zijn om er nog een extra formule omheen te bouwen die een 0 waarde negeert en dan niets weer geeft.

[Anoniem: 1059941]

Hallo Reptile en Itrme,

Bedankt dat je mij wil helpen. De foutcode die de formule aangeeft is #WAARDE!. Het gaat dus om een foutcode. Ik zal anders de originele formule hieronder plakken

=INDEX(Verkoopregister!F7:F10;VERGELIJKEN(B18;ALS(Verkoopregister!B7:B10=Verkoopfactuur!H22;Verkoopregister!A7:A10);0))

Is er misschien een mogelijkheid dat ik een printscreen op dit forum kan plaatsen, dan kan ik het misschien beter uitleggen.

[Paul1987]

Ik vind vaak dat je een meer elegante oplossing kunt vinden in VBA.
Zo kun je een 'on cell change' maken die 'luistert' of jij een bepaalde cel aanpast en vervolgens automatisch een stukje VBA aanroept. Deze code doet, volgens mij, precies wat jij wilt en zorgt ervoor dat je niet per ongeluk een formule kunt verwijderen. Ik heb hem overigens niet voorzien van comments.

De code gaat uit van 2 werkbladen (blad1 en blad2)
Deze werkbladen zijn alsvolgt gevuld
blad1:
jouw product 'tabel'

blad 2:
Factuurnr
<Factuur nummer bijv 2018001>

Op blad 2 komt dan, indien het factuurnummer voorkomt in de tabel vanaf regel 4 automatisch het bij behorende product. net zolang totdat 2018001 niet meer gevonden kan worden in de tabel op blad1.

VBScript: Code Blad2

Private Sub Worksheet_Change(ByVal Target As Range)

Const ws_range As String = "A2"

If Not Intersect(Target, Me.Range(ws_range)) Is Nothing Then
Call Module1.test

End If
End Sub

Visual Basic .NET: MODULE1

 

Sub test()

Dim wb As Workbook
Dim Bron_ws As Worksheet
Dim Doel_ws As Worksheet
Dim Fact_nr As Range
Dim fact_rij As Double

Set wb = ActiveWorkbook
Set Bron_ws = wb.Sheets("Blad1")
Set Doel_ws = wb.Sheets("Blad2")
Set Fact_nr = Doel_ws.Range("A2")
fact_rij = 4

Dim last_r
Dim r

With Bron_ws
last_r = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

If Fact_nr.Value <> "" Then

Dim last_product

With Doel_ws
last_product = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range(Cells(fact_rij, 1), Cells(last_product, 1)).Clear
End With

For r = 2 To last_r
If Bron_ws.Cells(r, 1).Value <> "" Then
If Bron_ws.Cells(r, 1).Value = Fact_nr.Value Then

Doel_ws.Cells(fact_rij, 1).Value = Bron_ws.Cells(r, 3).Value
fact_rij = fact_rij + 1

End If
End If
Next r
End If

End Sub

[ Voor 11% gewijzigd door Paul1987 op 05-04-2018 14:46 ]

[Reptile209]

Anoniem: 1059941 schreef op donderdag 5 april 2018 @ 14:25:
Hallo Reptile en Itrme,

Bedankt dat je mij wil helpen. De foutcode die de formule aangeeft is #WAARDE!. Het gaat dus om een foutcode. Ik zal anders de originele formule hieronder plakken

=INDEX(Verkoopregister!F7:F10;VERGELIJKEN(B18;ALS(Verkoopregister!B7:B10=Verkoopfactuur!H22;Verkoopregister!A7:A10);0))

Is er misschien een mogelijkheid dat ik een printscreen op dit forum kan plaatsen, dan kan ik het misschien beter uitleggen.

Je kunt via bijvoorbeeld imgur een plaatje uploaden en in je post gebruiken met de [img]-tag.

Je krijgt een #Waarde-fout omdat de ALS maar één waarde teruggeeft en daar zit de gezochte waarde niet tussen. Serieus: ga je even wat meer verdiepen in index/match want anders kom je er niet uit.

Een aanpak die de formule een heel stuk makkelijker maakt, is met een hulpkolom. In die kolom (op de sheet met alle factuurregels) voeg je met een & het factuur- en regelnummer samen (iets als =A1&B1). Vervolgens geef je de index/match dezelfde combinatie mee: MATCH(<cel met factuurnummer>&<cel met huidig regelnummer>;...). Dan wordt het opeens zoeken met maar één criterium en daar zijn legio voorbeelden van te vinden.

@Paul1987 ik denk niet dat TS veel verder komt met het copy-pasten van een lap VBA, als hij duidelijk al moeite heeft met een paar basis (ok, basis-plus) formules