=Prevsheet function

Question

I am using the =prevsheet(a9) function in my workbook and it works fine. However, when i open up another workbook at the same time the cells in the first workbook with the =prevsheet formulas show"VALUE" instead of the numbers. To get rid of the  VALUE i have to go into one of the cells and hit enter and all the numbers come back. How can i avoid this happening everytime i open of another workbook at the same time?

I use my workbook for cash forecasting purposes and it is risky to have the numbers change.

Answer 1

Try this simplified version. When I try it, opening another workbook doesn't affect the result of the formula.

Function PrevSheet(rng As Range)

    Application.Volatile

    PrevSheet = rng.Parent.Previous.Range(rg.Address)

End Function

Answer 2

No the problem is not solved. When I open up a second workbook while having the first workbook open with the prevsheet function,  the cells show Value in the first workbook.

The prevsheet function works in the first workbook but as soon as I open up another workbook my figures disappear. I have to click in one of the cells with the formula in workbook 1 and hit enter and then all my figures display again. i want to void this.,,

Answer 3

So the problem is solved? Good to hear that.

Answer 4

I'm not sure...i followed the steps listed on this site and it worked...

http://www.pcreview.co.uk/forums/transfer-variable-balance-one-worksheet-next-t3182078.html

Answer 5

PrevSheet is a custom function, not a built-in function. There are several versions of it around. Which one are you using?