MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Automatic Update of formulas


Posted by Steve Bell on September 14, 2001 7:01 AM

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


Posted by Juan Pablo on September 14, 2001 8:48 AM

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