Excel Automatic Update of formulas

Ok, I have written a VB function to return a value from another spreadsheet with the sheet name and cell as inputs:

Function GlobalLookup(SheetString As String, Cellloc As String) As Variant
Application.Volatile
GlobalLookup = Workbooks("Daily Input").Sheets(SheetString).Range(Cellloc)
End Function

Function InventorySumLookup(SheetString As String, Cellloc As String) As Variant
Application.Volatile
InventorySumLookup = Workbooks("Inventory summary ").Sheets(SheetString).Range(Cellloc)
End Function

I use these two functions in another spreadsheet to return values to add, multiply, etc...

Problem is, even with the volatite keyword in the functions, the functions still will not automatically update when the workbook is opened. Is there a way to do this? I noticed they will not update even when F9 is pressed.

Thanks,

Steve

Try this, maybe that'll do it (It's not THE solution, but i think it'll at least correcct the not-calculating thing). Put =VALUE(Yourformula...)

That way, i think, you force Excel to recalculate the value function, which recalculates YOUR function.

Hope that helps

Juan Pablo

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

Archive of Mr Excel Message Board

Automatic Update of formulas

Re: Automatic Update of formulas