0

I currently use a UDF to look up values from a table in my personal workbook and it works just as I need it to. However I was hoping to be able to share this UDF with other people I work with. I have mine saved in a .xlam and loaded as an addin. I am looking for a solution where I could reference one workbook shared on a shared server. The reason for this is the table needs updated one to several times per month and it would be easier if one person was updating the master table opposed to everyone updating their personal workbooks.

From what I have read you cannot use a UDF to reference a workbook which is not already open. I was hoping someone could let me know if there is another way to achieve what I am hoping to do. 

Function region(profit_center)
Dim pc_range As Range
Set pc_range = Workbooks("PERSONAL.xlsb").Sheets("Profit Center").Range("J1:K800")
region = Application.WorksheetFunction.VLookup(profit_center, pc_range, 2, False)
End Function

After reading some other answers I was under the impression that Index can read closed workbooks from Application.WorksheetFunction.Index but I get a value error when I try this code:

Function region_Index(profit_center)
Dim pc_range1 As Range
Dim pc_range2 As Range

Set pc_range1 = Workbooks("UNC filepath").Sheets("Profit Center").Range("J1:K800")
Set pc_range2 = Workbooks("UNC filepath").Sheets("Profit Center").Range("J1:J800")

region_Index = Application.WorksheetFunction.Index(pc_range1, Application.WorksheetFunction.Match(profit_center,pc_range2,0),2)
End Function

This code works when I am referencing an open workbook but I need it to work when referencing a closed workbook. Again I can use VBA and addins to achieve my goal but I am not sure the best way to do this.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Derek
  • 155
  • 13
  • 1
    Possible duplicate of [Excel formula to reference cells in another (closed) workbook with dynamic file path](https://stackoverflow.com/questions/36903320/excel-formula-to-reference-cells-in-another-closed-workbook-with-dynamic-file) Also see the list of related questions to that post (follow the link and then see the Related list on the right side of that page). – Ken White Oct 11 '19 at 02:52
  • @KenWhite Thank you for the link to the other question. From reading that I would think I could just write this with Index and Match functions since my file path will not be changing however it is still giving me a #Value error. – Derek Oct 11 '19 at 03:20
  • 1
    @Derek If you use `index`/`match` as a formula the other workbook needs to be open while you add the formula. After that a link to the other workbook exists and it doesn't need to be open anymore. If you want to do it with VBA you must open the other workbook to make the `WorksheetFunction.Index` work because it does not work with closed workbooks. So you can open the other workbook hidden in the background as read only. Then run your code on it and close it afterwards. The user won't see anything of that. Actually there are many tutorials how to open a workbook hidden in background. – Pᴇʜ Oct 11 '19 at 06:18

0 Answers0