Complex V Look up formula

Hello,

I have a slightly complex V Look up formula I am trying to compile.

I have some data in a workbook that looks like this: data.PNG from which I would like to extract data from into another workbook through V Look up.

I want the formula to search fore records with particular project numbers, in this case the project number in cell I2, and within the paint department (Column E). Once these records are found I then would like a sum of the hours in the K, N, Q and T columns. (The blue highlighted rows are the rows which meet the criteria to be summed up).

It is the hours I would like to be totaled and show in the cell H3 in this second workbook: vlookup.PNG

Can someone help me wrap my head around the complex formula needed?

Attach a sample workbook as we cannot work with images.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Ok John and Jacc, thanks! Here are the 2 workbooks attached to onedrive:

Here is the data workbook: https://1drv.ms/x/s!AiBf_qIewMKygQeLgBVLQwTKxf5v

Here is the 2nd workbook where I would like the vlook up to be done: https://1drv.ms/x/s!AiBf_qIewMKygQjLtfISM4JDBAy8

Please post w/books on this forum as many members will not visit file-hosting sites.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Originally Posted by JohnTopley

Please post w/books on this forum as many members will not visit file-hosting sites.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I'm sorry but the attach option on this forum is not working for me hence why I uploaded it to One Outlook One Drive which is really safe...

If you follow the instructions provided you will be able to attach a workbook.

Ok,I believe I have now attached the files. Thanks!

Try

in H4

=SUMPRODUCT(([data.xlsx]Sheet2!$K$2:$U$9)*([data.xlsx]Sheet2!$H$2:$H$9=$C3)*(ISNUMBER(SEARCH("HOURS",[data.xlsx]Sheet2!$K$1:$U$1))))

Originally Posted by JohnTopley

Try

in H4

=SUMPRODUCT(([data.xlsx]Sheet2!$K$2:$U$9)*([data.xlsx]Sheet2!$H$2:$H$9=$C3)*(ISNUMBER(SEARCH("HOURS",[data.xlsx]Sheet2!$K$1:$U$1))))

That works perfectly! Thank you John!!!

Hi John, the code you gave me works but when I try and amend the code for the actual form I am using, I run into issues. I'm not sure if it is because the actual workbook I am using to be looked up is a XLSM document type (XLSM)?...

Can you take another look please? I have re attached modified work books.

Could you also extend the v look up code for the total hours for both the paintwork and electrical departments for the project number 2016110340, which can be found in row 2 and row 9?...

Thank you!

Hi,

The formula doesn't seem to be working...it asks me to update the location source of the data, which is how I get this:

=SUMPRODUCT(--('C:\Users\sony\Desktop\Electronic Work Schedules\Electronic Work Schedules\[Work Schedule.xlsm]Data'!$E2:$E50="Paintwork"),--('C:\Users\sony\Desktop\Electronic Work Schedules\Electronic Work Schedules\[Work Schedule.xlsm]Data'!$I$2:$I$50=C6),('C:\Users\sony\Desktop\Electronic Work Schedules\Electronic Work Schedules\[Work Schedule.xlsm]Data'!$K$2:$K$50+'C:\Users\sony\Desktop\Electronic Work Schedules\Electronic Work Schedules\[Work Schedule.xlsm]Data'!$N$2:$N$50+'C:\Users\sony\Desktop\Electronic Work Schedules\Electronic Work Schedules\[Work Schedule.xlsm]Data'!$Q$2:$Q$50)+'C:\Users\sony\Desktop\Electronic Work Schedules\Electronic Work Schedules\[Work Schedule.xlsm]Data'!$T$2:$T$50)
It returns a value of XVALUE! back.

Thanks Jete. Doing as you suggested leaves me with the formula

=SUMPRODUCT(('Work Schedule.xlsm]Data'!$K$2:$U$50)*('Work Schedule.xlsm]Data'!$I$2:$I$50=$C6)*('Work Schedule.xlsm]Data'!$E$2:$E$50=I$4)*(ISNUMBER(SEARCH("HOURS",'Work Schedule.xlsm]Data'!$K$1:$U$1))))

Unfortunately I get this error...

error.PNGerror.PNG

My xlsm file is titled 'Work Schedule'...perhaps the space in the title is causing the problem?...

Ok, the form looks like this. It is the last section combo box

form.PNG

section4.PNG

The code is:

Private Sub SaveData()
  'Save the data from the controls into current row
  Dim C As MSForms.Control
  Dim varValue As Variant    'Must be variant to accept different types of data
  
    For Each C In Me.Controls
        If C.Tag <> "" Then
            'Get the value from the control
            varValue = C.Value
            Select Case C.Tag
                Case "A", "C", "G", "H", "I", "J", "M", "P", "S"
                    'These are a direct copy of their values to the worksheet
                    Worksheets("Data").Range(C.Tag & ThisRow.Row) = varValue
            
            
             Case "K", "L", "N", "O", "Q", "R", "T", "U"
                    If WorksheetFunction.IsText(C.Tag) Then   'Tests if "Complete" selected from DropDown
                        Worksheets("Data").Range(C.Tag & ThisRow.Row) = varValue
                    Else
                        'If not text thenthis value is a double number so convert text number to double number
                        Worksheets("Data").Range(C.Tag & ThisRow.Row) = CDbl(varValue)
                    End If
            
            
            
        
                Case "B", "D"
                    'These values must be valid dates so convert text date to date value
                    If IsDate(varValue) Then
                        Worksheets("Data").Range(C.Tag & ThisRow.Row) = CDate(varValue)
                    End If

Private Sub UserForm_Initialize()
    'A textbox for the ID and TimeStamp was missing on the form
    Me.TextBox1.Tag = "A" 'ID
    Me.TextBox2.Tag = "B" 'TimeStamp
    'You can hide it:
    'Me.TextBox1.Visible = False
    'Me.TextBox2.Visible = False
    
    'Your controls:
    Me.listGL.Tag = "C"
    Me.listDate.Tag = "D"
    Me.listEmployee.Tag = "G"
    Me.listProjectName.Tag = "H"
    Me.ProjectNumber.Tag = "I"
    
    
    Me.listSection1.Tag = "J"
    Me.listTask1.Tag = "K"
    Me.listHours1.Tag = "L"

    Me.listSection2.Tag = "N"
    Me.listTask2.Tag = "O"
    Me.listHours2.Tag = "P"

    Me.listSection3.Tag = "R"
    Me.listTask3.Tag = "S"
    Me.listHours3.Tag = "T"

    
    Me.listSection4.Tag = "V"
    Me.listTask4.Tag = "W"
    Me.listHours4.Tag = "X"

    
    
    
End Sub