Home Making a specific cell in each sheet returns a value above it and stay volatile
 I have a Excel with multiple sheets. In each sheet, I want a specific cell (let's call it BANK cell) to show the value of the cell above it. But there could be multiple empty cell above it until it reaches a cell that actually contains a numerical value. Each sheet will have its own BANK cell, and each BANK cell of each sheet should have its own value (at its own sheet). I write a VBA to achieve this. At first it looks successful. But then soon I realize, while I originally intended to let each sheet's BANK cell independent from each sheet, my VBA script actually make them all link together. Example, in sheet 1, if the cell above BANK cell has value 100, then the BANK cell automatically display 100, perfect. Then I go to sheet 2, do my work and eventually in sheet 2, the cell above BANK cell has value 200, and the BANK cell in sheet 2 also display 200, still perfect. But if I now go back to view sheet 1, I will find out that the BANK cell in sheet 1 is already become 200 instead of the intended 100. If I now reenter the formula in sheet 1 again, yes it will correct the BANK cell in sheet 1, but now sheet 2 is incorrect. How can I "unlink" them, so that the script only find value specific to a particular sheet? Function FindBalance() As Double Application.Volatile True Dim N As Long For N = 1 To 13 Dim BalanceCheck As Double Dim CellRow As Integer Dim Current_cell As Range Sheets(N).Activate Set Current_cell = Columns(3).Find("Total Money left in bank account", LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False) CellRow = Current_cell.Row BalanceCheck = 0 While BalanceCheck = 0 If Cells(CellRow - 1, "F").Value = "" Then CellRow = CellRow - 1 Else BalanceCheck = Cells(CellRow - 1, "F").Value End If Wend FindBalance = BalanceCheck Next N End Function