Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
SolvedPrivate

Lookups in another workbook

Posted on 2015-01-19
19
Medium Priority
?
34 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 19

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 19

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 19

Accepted Solution

by:
Richard Daneke earned 1000 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 1000 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 19

Expert Comment

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

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 49

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

704 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