Reference to cell in previous worksheet

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi All,

I am trying to work out how to add a reference to a cell
in the previous worksheet. It is for an accounting
spreadsheet and I want the final "Carried forward" amount
to appear in "brought forward" on the next sheet.

I Have done this in one sheet with the formula:
=('September 2003'!E33)
in the October sheet, but what I really need is to say:
=('current worksheet - 1'!E33)

Is this possible?

Thanks,
Steve
 
Place this function in a REGULAR module
Function ps()
x = ActiveSheet.Index - 1
ps = Sheets(x).Range("e33")
End Function
 
This might be more flexible. To use =px(a3) to get cell a3 value from
previous sheet in index.

Function px(x As Range)
'Application.Volatile
sh = ActiveSheet.Index - 1
px = Sheets(sh).Range(x.Address)
End Function

Calculation will not be automatic, even with application.volatile, for
changes in the other sheet so you will need a calculate event.
Private Sub Worksheet_Activate()
Calculate
End Sub
 
This might be more flexible. To use =px(a3) to get cell a3 value from
previous sheet in index.

Function px(x As Range)
'Application.Volatile
sh = ActiveSheet.Index - 1
px = Sheets(sh).Range(x.Address)
End Function
Click to expand...

More flexibility still would be achieved by making the worksheet offset an
argument. Also, and not so picky, index into the *WORKSHEETS* collection rather
than the Sheets collection unless you really, really want to fubar this function
with the possibility it might dereference a Chart sheet. See the following
linked article for an alternative.

http://www.google.com/groups?selm=uUMOMgaUBHA.1740@tkmsftngp07

Calculation will not be automatic, even with application.volatile, for
changes in the other sheet so you will need a calculate event.
Private Sub Worksheet_Activate()
Calculate
End Sub
Click to expand...
...

OK, so how does this help if worksheet B contains a formula with a worksheet
offset call to worksheet A, and a change is made in worksheet A but the user
doesn't activate worksheet B? What if there's a chain of calculations,

B!X99: =px(X99)+X98
C!X99: =px(X99)+X98

If A:X99 changes, how do you percolate that changes into the entire dependency
chain? The most reliable way to make any formula (that's *FORMULA*, not
function) volatile is to add a volatile term to is. If these px() calls should
return numbers, try =px(X99)+X98+0*RAND(). Alternatively, use Workbook event
handlers SheetChange and SheetCalculate to force global recalc. The former
approach provides finer granularity.
 
Back
Top