reference to another Google sheet by its index in formula

Question

I'm trying to get a generic formula in a google sheet to refer to a cell in the "previous" sheet. This means I need to be able to reference it without using its name, but rather using its index number.

It would look something like this.

=getsheetbyindex(thissheetindex()-1)!A1

I'd like to do this without a script. I've searched and tried to look into google's help, but couldn't find a way to access a sheet by anything but its name.

Edit : it's ok to use a script function, what I meant is that I want to do this in the cell formula itself, not in a script.

Answer 1

script:

function SHEET(input) {
try {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets() ;
  if( (input>0) && (input <= sheets.length)) return sheets[(input-1)].getName() ;
  else return "invalid sheet #" ;
}
catch( err ) {
  return "#ERROR!" 
}
}

formula for first sheet:

=SHEET(1)

formula for 5th sheet:

=SHEET(5)

etc.