Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 35
  • Last Modified:

Lookups in another workbook

I have a rather easy sumproduct lookup formula set up, to look up data on sheet Data in the attached example file. The complication comes that I want to be able to perform the same lookup if Data sheet is located in a different workbook, which I am opening with a VBA code below. Can I somehow use VBA worksheet function lookup to perform the same type of a lookup that iterates through cells C3:D8 on the Lookup sheet? I can store just the values in C3:D8.

book1.xlsx
0
Dmitriy Kritskiy
Asked:
Dmitriy Kritskiy
  • 7
  • 4
  • 2
  • +4
2 Solutions
 
FloraCommented:
HERE IS THE CODE

see it and run it in the attached workbook,.

Sub MACRO()

Application.ScreenUpdating = False
Dim finalrow1 As Long
Dim finalrow2 As Long
finalrow1 = Sheets("Lookup").Cells(Rows.Count, 2).End(xlUp).Row
finalrow2 = Sheets("Data").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Lookup").Select
    introw = 3
Do Until introw = finalrow1 + 1
With Sheets("Lookup")

    
    Cells(introw, 3).Formula = "=SUMPRODUCT(('Data'!$A$3:$A" & finalrow2 & "=C$2)*('Data'!$C$2:$E$2=$A" & introw & ")*('Data'!$B$3:$B" & finalrow2 & "=$B" & introw & ")*('Data'!$C$3:$E" & finalrow2 & "))"
    
    Cells(introw, 4).Formula = "=SUMPRODUCT(('Data'!$A$3:$A" & finalrow2 & "=D$2)*('Data'!$C$2:$E$2=$A" & introw & ")*('Data'!$B$3:$B" & finalrow2 & "=$B" & introw & ")*('Data'!$C$3:$E" & finalrow2 & "))"
     End With
    introw = introw + 1
   
    DoEvents
Loop
Application.ScreenUpdating = True
End Sub

Open in new window

book1.xlsm
0
 
Dmitriy KritskiyAuthor Commented:
perhaps, I wasn't totally clear, the challenge is if Data worksheet is located in a different workbook, that I am opening with the code I attached initially, so I need to pass the variable for the name of the worksheet in a different workbook inside the lookup.
data.xlsx
lookup.xlsm
0
 
Phillip BurtonCommented:
No idea what you are talking about. Please explain with some details.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Dmitriy KritskiyAuthor Commented:
In the target workbook (Lookups) there is a vba code to open a source workbook (Data). The goal is for the lookups in the target workbook to get data from that source workbook and paste values. My understanding is that I need to loop through cells in Lookup workbook with VBA worksheetfunction formula that uses a variable thats declared for the source sheet:
Dim data As Worksheet
Set data = sourceWorkbook.Worksheets("Data")
0
 
Phillip BurtonCommented:
So -
introwsource = 2
for introw = 1 to 10000
  if data.cells(introw,1) = "HI" then
      cells(introwsource, 1) = data.cells(introw,2)
      introwsource = introwsource + 1
  end if
next

Is that what you mean?
0
 
Dmitriy KritskiyAuthor Commented:
no, please see attached:
in source are groups and buckets as column attributes, and categories as top row attribute. Target workbook is organized in a different format. The lookup is done easily with a sumproduct formula if it's all in one workbook, but I need to be able to do the lookup if data is in a different workbook.
lookup.xlsm
data.xlsx
0
 
Richard DanekeCommented:
Syntax for a workbook is to enclose the workbook name in square brackets:
[ workbook name ] sheet name ! reference
so [data.xlsx]'data'!$A$3:$A7 when the workbook is in the same folder.

To help, I create the formulas in macros or in the cells using the worksheet in the same workbook.  When I am ready to push the data worksheet to a new file, I use Move or Copy worksheet... to move the worksheet to a new file.  Excel adjusts the Macros and formulas to reflect the new location.
0
 
Richard DanekeCommented:
open the macro workbook and note the syntax in the worksheet table for the workbook syntax.
lookupBook1.xlsm
Book1.xlsx
0
 
Dmitriy KritskiyAuthor Commented:
this is still not what I am looking for. Source workbook name can be anything, hence there is a dialogue that prompts user to open a source file in the Sub OpenWorkbook(). The source sheet in the source workbook will be always the same = Data. So the lookup needs to utilize the following declaration:

Set sourceWorkbook = Application.Workbooks.Open(sourceFilename)  

Dim data As Worksheet
Set data = sourceWorkbook.Worksheets("Data")

So I need a lookup that uses 'data' variable that we just declared.
0
 
Dmitriy KritskiyAuthor Commented:
so the code would be:
Sub OpenAndLookup()

Application.ScreenUpdating = False

Dim filter As String
Dim caption As String
Dim sourceFilename As String
Dim sourceWorkbook As Workbook
Dim targetWorkbook As Workbook

' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook

' get the source workbook
filter = "Excel Workbooks (*.xlsx; *.xlsm),*.xlsx;*.xslm"
caption = "Please Select an input file "

sourceFilename = Application.GetOpenFilename(filter, , caption)

Set sourceWorkbook = Application.Workbooks.Open(sourceFilename)

Dim data As Worksheet
Set data = sourceWorkbook.Worksheets("Data")

Dim lookup As Worksheet
Set lookup = targetWorkbook.Worksheets("Lookup")


Dim finalrow1 As Long
Dim finalrow2 As Long
finalrow1 = lookup.Cells(Rows.Count, 2).End(xlUp).Row
finalrow2 = data.Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Lookup").Select
    introw = 3
Do Until introw = finalrow1 + 1
With Sheets("Lookup")

 Cells(introw, 3).Formula = "=SUMPRODUCT(('Data'!$A$3:$A" & finalrow2 & "=C$2)*('Data'!$C$2:$E$2=$A" & introw & ")*('Data'!$B$3:$B" & finalrow2 & "=$B" & introw & ")*('Data'!$C$3:$E" & finalrow2 & "))"
    
    Cells(introw, 4).Formula = "=SUMPRODUCT(('Data'!$A$3:$A" & finalrow2 & "=D$2)*('Data'!$C$2:$E$2=$A" & introw & ")*('Data'!$B$3:$B" & finalrow2 & "=$B" & introw & ")*('Data'!$C$3:$E" & finalrow2 & "))"
     End With
    introw = introw + 1
   
    DoEvents
Loop

sourceWorkbook.Close

Application.ScreenUpdating = True

End Sub

Open in new window


but I need a right syntax in the sumproduct in line 38 and 40 so that I am referring to data variable not hardcoded Data sheet.
0
 
Richard DanekeCommented:
So, you need to substitute your variable for the workbook and worksheet for the 'Data' in your functions!
I changed the data variable to dataWS to make the change more obvious, but you should only need to concatenate with the variable name as seen below.
I did a replace on 'Data' with " & dataWS & "  The quotes character effect the delimiting of the references.

Sub OpenAndLookup()

Application.ScreenUpdating = False

Dim filter As String
Dim caption As String
Dim sourceFilename As String
Dim sourceWorkbook As Workbook
Dim targetWorkbook As Workbook

' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook

' get the source workbook
filter = "Excel Workbooks (*.xlsx; *.xlsm),*.xlsx;*.xslm"
caption = "Please Select an input file "

sourceFilename = Application.GetOpenFilename(filter, , caption)

Set sourceWorkbook = Application.Workbooks.Open(sourceFilename)

Dim dataWS As Worksheet
Set dataWS = sourceWorkbook.Worksheets("Data")

Dim lookup As Worksheet
Set lookup = targetWorkbook.Worksheets("Lookup")


Dim finalrow1 As Long
Dim finalrow2 As Long
finalrow1 = lookup.Cells(Rows.Count, 2).End(xlUp).Row
finalrow2 = data.Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Lookup").Select
    introw = 3
Do Until introw = finalrow1 + 1
With Sheets("Lookup")

 Cells(introw, 3).Formula = "=SUMPRODUCT((" & dataWS & "!$A$3:$A" & finalrow2 & "=C$2)*(" & dataWS & "!$C$2:$E$2=$A" & introw & ")*(dataWS & !$B$3:$B" & finalrow2 & "=$B" & introw & ")*(" & dataWS & "!$C$3:$E" & finalrow2 & "))"
 Cells(introw, 4).Formula = "=SUMPRODUCT((" & dataWS & "!$A$3:$A" & finalrow2 & "=D$2)*(" & dataWS & "!$C$2:$E$2=$A" & introw & ")*(" & dataWS & "!$B$3:$B" & finalrow2 & "=$B" & introw & ")*(" & dataWS & "!$C$3:$E" & finalrow2 & "))"

     End With
    introw = introw + 1
   
    DoEvents
Loop

sourceWorkbook.Close

Application.ScreenUpdating = True

End Sub

Open in new window

0
 
Dmitriy KritskiyAuthor Commented:
not working, getting some minor errors, which I can fix but at the end of the day it gives me "Object doesn't support this type of property or method error" on line 38.
0
 
ChloesDadCommented:
Line 38 has an error

Cells(introw, 3).Formula = "=SUMPRODUCT((" & dataWS & "!$A$3:$A" & finalrow2 & "=C$2)*(" & dataWS & "!$C$2:$E$2=$A" & introw & ")*(dataWS & !$B$3:$B" & finalrow2 & "=$B" & introw & ")*(" & dataWS & "!$C$3:$E" & finalrow2 & "))"

should be

Cells(introw, 3).Formula = "=SUMPRODUCT((" & dataWS & "!$A$3:$A" & finalrow2 & "=C$2)*(" & dataWS & "!$C$2:$E$2=$A" & introw & ")*(" & dataWS & "!$B$3:$B" & finalrow2 & "=$B" & introw & ")*(" & dataWS & "!$C$3:$E" & finalrow2 & "))"

I then get this in the cell when dataWS is Sheet2 introw=1 and finalrow2=10

=SUMPRODUCT((Sheet2!$A$3:$A10=C$2)*(Sheet2!$C$2:$E$2=$A1)*(Sheet2!$B$3:$B10=$B1)*(Sheet2!$C$3:$E10))

Which excel is happy about
0
 
Richard DanekeCommented:
Chloe's Dad is correct.   There was an error in my first line.
0
 
ProfessorJimJamCommented:
Dmitriy Kritskiy,

do you need further help on this question?   i can chip in and help you if you need?

please provide feedback.
0
 
Dmitriy KritskiyAuthor Commented:
Hi I've been away, I need to review the solutions, and check if they are working.
0
 
Martin LissRetired ProgrammerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now