Closed Workbook Lookup/Sumproduct with Formula Variables

S

screech

Active Member
Joined
May 27, 2004
Messages
296
Hello all,

I have run into a little snag and much frustration when trying to lookup values from a closed workbook. I was able to use sumproduct to look up a value from a closed workbook and it looks like it is working OK.

Code: 
=SUMPRODUCT(--('C:\test\[test book.xlsx]2000'!$A$1:$A$1000=F15),--('C:\test\[test book.xlsx]2000'!$B$1:$B$1000=G15),('C:\test\[test book.xlsx]2000'!$D$1:$D$1000))

In the closed workbook "test book.xlsx", there is data on the sheet "2000" that I want to look up. One criteria is in column A and matched to a cell on my current sheet (F15), another in column B matched to another criteria (G15), and I want to return the matching sum in column D of the closed workbook. This code works.

The problem now becomes that I want to use the values typed into a cell to reference where the path of the file name, the name of the reference workbook, and the sheet name. These would be typed into different cells. For example, I would put something like "C:\test\[test book.xlsx]" in one cell, and "2000" in another cell and those values would be used as variables in the sumproduct formula. The goal would be to be able to enter new values in those cells based on where the workbook is saved, what the file name is, and what the sheet is named, and have the formula recalculate using the new information.

I can't seem to use cell values in my sumproduct formula. I've searched the forums here and saw things about the "pull" function (but won't work with Excel 2007?), some UDF functions with VBA code using "evaluate" to get the sumproduct... but I'm not making sense of it so nothing is working.

Can this variable-filled formula be used in Excel or is there something else I can try? I'm having problems with the correct way to write the formula so any help would be greatly appreciated. Thanks!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
I am wondering, can using variable worksheet or workbook names contained in other cells not be used in an excel formula?
 
Upvote 0
I have started to write a UDF to handle this problem. I've never successfully created a UDF before so this is my first attempt. I started to test some code in a module to see if I can at least get the basics of the UDF correct. This is what I came up with so far as an experiment:

Code: 
Dim fpath As String
Dim fname As String
Dim sname As String

fpath = "C:\test\"
fname = "test book.xlsx"
sname = "2000"

Value1 = Application.Evaluate("=SUMPRODUCT(--('" & fpath & "[" & fname & "]" & sname & "'!$A$1:$A$1000=F15),--('" & fpath & "[" & fname & "]" & sname & "'!$B$1:$B$1000=G15),('" & fpath & "[" & fname & "]" & sname & "'!$D$1:$D$1000))")

I am trying to create a couple of variables in my code for the filepath, filename, and sheetname so I can use them in the original Sumproduct code in my first post. I am trying to test the Evaluate code above so I just assigned them values inside the code. The problem is that the value of "Value1" is Error 2023 when I run through this section of code. Does it look like I'm not using Evaluate properly?
 
Upvote 0
So I have some confusion and I don't know how to resolve it. Can someone please compare the following lines from my code and tell me what I am doing wrong? Using the variables defined above ...

This code works:
Code: 
ActiveCell.Formula = _
        "=SUMPRODUCT(--('" & fpath & "[" & fname & "]" & sname & "'!$A$1:$A$1000=F15),--('" & fpath & "[" & fname & "]" & sname & "'!$B$1:$B$1000=G15),('" & fpath & "[" & fname & "]" & sname & "'!$D$1:$D$1000))"

This code does not work (Error 2023):
Code: 
Value1 = Evaluate("=SUMPRODUCT(--('" & fpath & "[" & fname & "]" & sname & "'!$A$1:$A$1000=F15),--('" & fpath & "[" & fname & "]" & sname & "'!$B$1:$B$1000=G15),('" & fpath & "[" & fname & "]" & sname & "'!$D$1:$D$1000))")

I thought "Evaluate" was just going to take the result of a formula as if I was typing it into a cell in excel. Why would one work and the other not work?

Thank you to anyone for any help. I keep trying things but it doesn't seem to work out right.
 
Upvote 0
Well after numerous message board searches and changes to my code (none of which worked) ... and judging by the silence of this message board ... I guess I'm just about ready to give up on trying to get Excel to do what I want it to do. I still don't get why the code for the formula in the cell works OK but to "evaluate" the same formula in VBA gives me an error. Thank you to anyone who happened to see this post and give it some thought.
 
Upvote 0
well i have the same problem and i put some effort in this, but to no avail. Eval just does not seem to get vlookup to work in closed workbooks.

and an interesting point: it always returns "1" for the MATCH function w/ closed wbooks.

if i come across any (or the next best) solution, i'll post it here.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
interrogating closed workbooks
Replies
6
Views
229
Eric W
Eric W
C
  • Question
Lookup with sumproduct
Replies
3
Views
129
chris0b
C
J
  • Question
Formula to find date and return column number
Replies
2
Views
159
jc352
J
rehanahmad
  • Question
SUMIF With "Indirect" link to closed workbook
2
Replies
11
Views
444
RoryA
RoryA
A
  • Question
SUMIFS exclude value if partial match in list/named range
2
Replies
18
Views
427
Alex Blakenburg
A

Forum statistics

Threads
1,214,927
Messages
6,122,311
Members
449,080
Latest member
jmsotelo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top