SolvedPrivate

Lookups in another workbook

Posted on 2015-01-19
19
28 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 6

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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
 

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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

821 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