=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

After pressing Alt+F11 to activate the Visual Basic Editor, select Insert | Module or press Alt+I, M to create a new, empty code module.

Copy and paste the following code into the module window:

Function PrevSheet(rng As Range)

    Application.Volatile

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

End Function

Answer 2

I am having the same problem. I am copying the pasting the data exactly as you have written above. 

I think I may be entering this in to VBA incorrectly as I have zero understanding of how the code is derived.

Can you confirm how you would actually add this to you spreadsheet.

Currently I am pressing alt with f11, pasting:

Function PrevSheet(rng As Range)

    Application.Volatile

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

End Function

into the box on the right and I am just getting the result NAME?, when I enter =prevsheet(a2) into the cell on the next worksheet.

All I need to do is at the start date for the beginning of the week on each sheet. IE Week1 31/12/12, week 2, 07/01/13, week 3, 14/01/13 etc.

I need to ref the previous sheet then just add 7, but don't fancy manually changning the worksheet number for 52 weeks.

I am positive I am missing out an obvious step.

Thanks

Answer 3

I'm sorry, I can't explain why it behaves that way for you.

Answer 4

Hey, i just tried it and it still shows "#VALUE! in the first workbook after opening the 2nd workbook. Also, this time i was not able to make my values reappears by going in to a cell and clicking F2 and hitting ENTER. When i do this using the formula provided it comes back "#VALUE!.

I really would like this to work because the other option is for me to manually like each cell with totals to the same cell on the previous tab. That will take a long time becuase even if i autofill the formula on the entire workbook i don't know how to get it to pull the data from the previous tab cell instead of the first tab. When i link the cells it pulls the right cell in each tab but the tab it is pulling the data from is always the first tab.