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
GlobalLookup = Workbooks("Daily Input").Sheets(SheetString).Range(Cellloc)
End Function

Function InventorySumLookup(SheetString As String, Cellloc As String) As Variant
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.



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