SolvedPrivate

Lookups in another workbook

Posted on 2015-01-19
19
23 Views
Last Modified: 2016-02-12
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
Comment
Question by:Dmitriy Kritskiy
  • 7
  • 4
  • 2
  • +4
19 Comments
 
LVL 5

Expert Comment

by:Flora
ID: 40558880
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
 

Author Comment

by:Dmitriy Kritskiy
ID: 40559735
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40566175
No idea what you are talking about. Please explain with some details.
0
 

Author Comment

by:Dmitriy Kritskiy
ID: 40566458
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40566467
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
 

Author Comment

by:Dmitriy Kritskiy
ID: 40566504
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
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 40566676
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
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 40566685
open the macro workbook and note the syntax in the worksheet table for the workbook syntax.
lookupBook1.xlsm
Book1.xlsx
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Dmitriy Kritskiy
ID: 40566793
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
 

Author Comment

by:Dmitriy Kritskiy
ID: 40566859
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
 
LVL 18

Accepted Solution

by:
Richard Daneke earned 250 total points
ID: 40567383
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
 

Author Comment

by:Dmitriy Kritskiy
ID: 40570410
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
 
LVL 15

Assisted Solution

by:ChloesDad
ChloesDad earned 250 total points
ID: 40576165
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
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 40578456
Chloe's Dad is correct.   There was an error in my first line.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40590682
Dmitriy Kritskiy,

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

please provide feedback.
0
 

Author Comment

by:Dmitriy Kritskiy
ID: 40615165
Hi I've been away, I need to review the solutions, and check if they are working.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40667499
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now