Using formula from another worksheet.

[Patti]

I have similar spreadsheets containing multiple
worksheets, with separate names on the tabs. How can I
set up a template to refer to a specific cell in the
worksheet just previous, no matter what the name of the
tab. Is there a universal reference to the cell #?

 

[Don Guillett]

Put this function in a REGULAR module
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