Lookup data from Old entries to latest

[nabeelahmed]

Hi Friends,,, Hope alss well... I am working on book where i have 2 sheets Lookup Data sheet is "Tools & Consumables" and where i want to extract data is"Issuance"
- I want that in sheet "Issuance"when i will enter Tag# 5737 in cell E6 the description in cell F6 & Unite Price in cell M6 should be extracted from data sheet from older entry first with having some balance on hand and when that balance finished then data should be extracted from the next entry and so on...

Thanks & REgards,

Spares & Consumable Register.xlsx
	L								T	U								AC	AD
3	Description of spare parts								received date	Tag #								Balance on Hand	Unit Price
4	Bechem Berulub VPN 13								1-Oct-2019	5737								10	5000
5	EPTFE Gasket 3mm 1" 150lbs RF ANSI B16.21 Spira								1-Oct-2019	8579								5
6	Cylindrical Roller Bearing NU306 ECP SKF								1-Oct-2019	7415								2
7	Bearing 7306 BECEP SKF								1-Oct-2019	7416								3
8	Shaft OD 34x380 mm L SS Fabricated as per Sample								1-Oct-2019	DI3867								1
9	Bechem Berulub VPN 13-1								1-Sep-2020	5737								14	4950
10	EPTFE Gasket 3mm 1" 150lbs RF ANSI B16.21 Spira								1-Sep-2020	8579								47
11	PTFE Gore Ring Gasket 1.5" x 3mm #150								1-Sep-2020	2006								25
12	Bearing 7306 BECEP SKF								1-Sep-2020	7416								22
13	Shaft OD 34x380 mm L SS Fabricated as per Sample								1-Sep-2020	DI3867								11
14	Bechem Berulub VPN 13-2								3-Sep-2020	5737								5	4700
Tools & Consumables

Spares & Consumable Register.xlsx
	D	E	F	G	H					M
5	Date	Tag #	Item Description	UOM	Qty Issued					Unit Price
6	5-Sep-2020	5737	Bechem Berulub VPN 13	Drum	1
7
8
9
10
11
12
13
Issuance

 

[nabeelahmed]

In message #5 you wrote:

There is a column AC named "Balance on Hand" and I interpreted this is the column to check for the "balance" higher than Zero.
So, with reference to your information, I assumed that the formula had to return values from row 4 (because Balance on hand is 10), or from row 7 (if AD4 is Zero and AD7 is >0), or from row 14 (if also AD7 is Zero and A14 is >0), and so on checking all the lines in "Tool&Consumable".
Obviously when no any line is suitable to be picked (balance always Zero) the formula have nothing to pick. But this can be easily corrected using "IFERROR" (=ISERROR(TheFormula,WhatReturnInCaseTheFormulaReturnsAnError)) and inserting a default value to pick if the standard

From your latest message I understand that column AD is not the column to look at for checking the "balance" and new columns are shown (hidden in previous messages); so please explain again how checking for "balance"
In snapshot 1 of your latest message all the rows have the remark "must show this Row unit price"; this is confusing me because you write that both 5000 /4950 /4400 must be shown

I cannot decode what you mean... What are "the pevious entries" that should not be modified?

So in general please restate the problem given the confusion I have in mind. Maybe attaching a real file with some demo data (and not only the snapshots) could make things easier to explain. For sharing a file you have to use an indipendent fileshare service, for example at filedropper.com (then publish the link that filedropper will give you after the file upload)

Bye

Dear Anthony please find below links

Screen Shot Video + Excel Workbook Excel Working/Excel.avi and 1 more file
Only Excel Workbook Working.xlsx

 

[Anthony47]

So it is correct that we check column AC to find a row with available balance, but you need that the information be static and they don't change when the information on sheet Tools&Consumables changes.

Thus we cannot use formulas, but need to switch to vba.

So rightclick on the tab with the label Issuance, and select Display code; this will open the "vba project" of your file on the "vba class module" that belongs to Issuance worksheet
Copy the following code and paste it into the right frame of that Module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim MaxTnC As Long, myMatch, I As Long, CTag
Dim TnC As Worksheet, Rispo
'
If Target.Column = 5 Then
Set TnC = ThisWorkbook.Sheets("Tools & Consumables")
MaxTnC = 10000
Cancel = True
    If Target.Value <> "" Then
        If Target.Offset(0, 1).Value <> "" Then
            Rispo = MsgBox("This row is already compiled with data. Do you want to overwrite current data?" _
               & vbCrLf & "Click Yes to overwrite them or No to abort the process", vbYesNo, "Make a choice")
            If Rispo <> vbYes Then Exit Sub
            Target.Offset(0, 1).ClearContents
            Target.Offset(0, 8).ClearContents
        End If
        CTag = Target.Value
        If Application.WorksheetFunction.CountIf(TnC.Range("U1:U" & MaxTnC), CTag) > 0 Then
            For I = 1 To MaxTnC
            DoEvents
                myMatch = Application.Match(CTag, TnC.Range("U" & I & ":U" & MaxTnC), False)
                If IsError(myMatch) Then
                    MsgBox ("There are no lines with available Balance for tag " & Target.Value & _
                    vbCrLf & "The process is aborted")
                    Exit Sub
                Else
                    myMatch = myMatch + I - 1
                    If TnC.Cells(myMatch, "AC").Value > 0 Then
                        Target.Offset(0, 1).Value = TnC.Cells(myMatch, "L").Value
                        Target.Offset(0, 8).Value = TnC.Cells(myMatch, "AD").Value
                        Beep
                        Exit Sub
                    Else
                        I = myMatch
                    End If
                End If
            Next I
        Else
            MsgBox ("Zero entries in sheet Tools&Consumables for tag " & Target.Value & _
               vbCrLf & "The process is aborted")
        End If
    End If
End If
End Sub

At this point the workbook need to be saved in "xlsm" (Macro enabled) format

Now go to sheet Issuance and remove the formulas we have been talking up to now
Insert a tag name in column E, then DoubleClick on that cell

At this point the macro should look for the correct line on Tools&Consumable and pick the Description and the UnitPrice
If no any suitable line is found, a message will warn you
If the Description for the doubleclicked line is already compiled, then a message will ask if you want to overwrite that information or not
If the typed tag doesn't exist in Tools&Consumable then a message will warn you

Try and let us know...

 

[nabeelahmed]

So it is correct that we check column AC to find a row with available balance, but you need that the information be static and they don't change when the information on sheet Tools&Consumables changes.

Thus we cannot use formulas, but need to switch to vba.

So rightclick on the tab with the label Issuance, and select Display code; this will open the "vba project" of your file on the "vba class module" that belongs to Issuance worksheet
Copy the following code and paste it into the right frame of that Module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim MaxTnC As Long, myMatch, I As Long, CTag
Dim TnC As Worksheet, Rispo
'
If Target.Column = 5 Then
Set TnC = ThisWorkbook.Sheets("Tools & Consumables")
MaxTnC = 10000
Cancel = True
    If Target.Value <> "" Then
        If Target.Offset(0, 1).Value <> "" Then
            Rispo = MsgBox("This row is already compiled with data. Do you want to overwrite current data?" _
               & vbCrLf & "Click Yes to overwrite them or No to abort the process", vbYesNo, "Make a choice")
            If Rispo <> vbYes Then Exit Sub
            Target.Offset(0, 1).ClearContents
            Target.Offset(0, 8).ClearContents
        End If
        CTag = Target.Value
        If Application.WorksheetFunction.CountIf(TnC.Range("U1:U" & MaxTnC), CTag) > 0 Then
            For I = 1 To MaxTnC
            DoEvents
                myMatch = Application.Match(CTag, TnC.Range("U" & I & ":U" & MaxTnC), False)
                If IsError(myMatch) Then
                    MsgBox ("There are no lines with available Balance for tag " & Target.Value & _
                    vbCrLf & "The process is aborted")
                    Exit Sub
                Else
                    myMatch = myMatch + I - 1
                    If TnC.Cells(myMatch, "AC").Value > 0 Then
                        Target.Offset(0, 1).Value = TnC.Cells(myMatch, "L").Value
                        Target.Offset(0, 8).Value = TnC.Cells(myMatch, "AD").Value
                        Beep
                        Exit Sub
                    Else
                        I = myMatch
                    End If
                End If
            Next I
        Else
            MsgBox ("Zero entries in sheet Tools&Consumables for tag " & Target.Value & _
               vbCrLf & "The process is aborted")
        End If
    End If
End If
End Sub

At this point the workbook need to be saved in "xlsm" (Macro enabled) format

Now go to sheet Issuance and remove the formulas we have been talking up to now
Insert a tag name in column E, then DoubleClick on that cell

At this point the macro should look for the correct line on Tools&Consumable and pick the Description and the UnitPrice
If no any suitable line is found, a message will warn you
If the Description for the doubleclicked line is already compiled, then a message will ask if you want to overwrite that information or not
If the typed tag doesn't exist in Tools&Consumable then a message will warn you

Try and let us know...

Dear Anthony, Thanks for your help and giving a lot of time that is fantastic idea to use VBA but it will make the job very lengthy as i have to issue hundred of items a day so i have to click hundred of cells to get data.. moreover i have many more other columns for which i wanted to extract data automatically these 2, 3 columns were only example to learn... can't we make it more easy ?

 

[Anthony47]

You may replace the first line of my macro with the following:

Private Sub Worksheet_Change(ByVal Target As Range)

In this way its job will be done as soon as you enter a tag# in column E

Bye

 

[nabeelahmed]

You may replace the first line of my macro with the following:

Private Sub Worksheet_Change(ByVal Target As Range)

In this way its job will be done as soon as you enter a tag# in column E

Bye

Hi Anthony. hope you will be good.. i did as you suggested now i am facing three below issues.. Please look into it if you can do help on these issues...!!!
1- Whenever i am deleting entered data in issuance sheet i.e. Tag# and Item Description i am getting error snap shot attached below
2- When i delete Tag number which i had enter in sheet Issuance column E to extract data all data which was extracted should delete automatically.
3- When i update/Edit data in Sheet Tools & Consumable for the row/Line items we ave already entered/extracted in Issuance sheet should update automatically.


Thanks & Regards

 

[Anthony47]

Error #1 could arise if you try the macro without removing the formulas and the formulas return an #Error; you should "Debug" the code locally, if this isn't the problem
To deal with request #2, you may add these 3 lines in this position:

        End If                                      'Existing
    Else                                            'Add
            Target.Offset(0, 1).ClearContents       'Add
            Target.Offset(0, 8).ClearContents       'Add
    End If                                          'Existing
End If
End Sub

Request #3 cannot be dealt with the current approach, and basically requires that your overall needs and possible approaches are examined; but this hardly can be done in a forum.
So, in no one will offer a solution here, my suggestion is that you contact a local consultant

Sorry...

 

[nabeelahmed]

Error #1 could arise if you try the macro without removing the formulas and the formulas return an #Error; you should "Debug" the code locally, if this isn't the problem
To deal with request #2, you may add these 3 lines in this position:

        End If                                      'Existing
    Else                                            'Add
            Target.Offset(0, 1).ClearContents       'Add
            Target.Offset(0, 8).ClearContents       'Add
    End If                                          'Existing
End If
End Sub

Request #3 cannot be dealt with the current approach, and basically requires that your overall needs and possible approaches are examined; but this hardly can be done in a forum.
So, in no one will offer a solution here, my suggestion is that you contact a local consultant

Sorry...

Dear Anthony, No problem what you have done for me is Great & admirable... Thank you

Regards