SolvedPrivate

Lookups in another workbook

Posted on 2015-01-19
19
18 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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 45

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 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

12 Experts available now in Live!

Get 1:1 Help Now