Copying Reference to previous sheets

[MarcB]

If I have a reference to a cell or range in the preceding sheet I am in at the moment, is there a way that if I copy that sheet with that reference to a new sheet following, that the reference will update to now refer to the preceding sheet. As an example, if I have a formula in SheetB refering to a cell in SheetA, and I copy the sheet SheetB and place it after, say sheetC, is it possible to have a cell reference that updates to now refer to that same cell or range but updated to include what is now the sheet before. Similar in concept to how a relative reference updates automatically within a sheet, but instead updating across sheets i.e. continuing to still reference the preceding sheet.

 

[bebo021999]

If I have a reference to a cell or range in the preceding sheet I am in at the moment, is there a way that if I copy that sheet with that reference to a new sheet following, that the reference will update to now refer to the preceding sheet. As an example, if I have a formula in SheetB refering to a cell in SheetA, and I copy the sheet SheetB and place it after, say sheetC, is it possible to have a cell reference that updates to now refer to that same cell or range but updated to include what is now the sheet before. Similar in concept to how a relative reference updates automatically within a sheet, but instead updating across sheets i.e. continuing to still reference the preceding sheet.

Try this:
=INDIRECT("'"&INDEX(AllSh,,MATCH(CurSh,AllSh,0)-1)&"'!A1")
will alway link to cell A1 of prv.sheet.