Geek Answers Handbook

Use formulas from other books?

My group is currently using Excel workbooks to track employee performance. Each employee has his own workbook, and they are identical, except for the data entered by the user. This book has custom VBA formulas that take data and generate a point base.

What I would like to do is move these formulas to another book, so if the calculation needs to be changed, it can be changed in one source document, and not in hundreds of books.

I imagine something like ...

='[Source Formula Workbook.xlsm]'!Formula_A(A1, A2, A3...)

... where A1, A2, A3 are cells in the user's workbook, and this returns the Formula_A value from my hypothetical source book. What is the right way to accomplish this?

+4
source share
1 answer

The usual way to do this is to move the VBA functions in addition to the XLA / XLAM and save the addition on a network drive. Each employee must then use the Excel Addin Manager (or installer) to add network XLA to their Excel.
see http://www.cpearson.com/excel/createaddin.aspx on how to create an addon.
You can create a simple XLSM installer file that uses VBA to add XLAM to the add-on list:

 With AddIns.Add(FileName:=MyNetworkAddInPath)
        .Installed = True
        End With
+7
source

More articles:


All Articles