Formula ignoring sheet name but still referencing sucessive sheets in workbook

Hi all,

not sure if this is possible without a Macro, but does anyone know of a formula to return the info in another sheet in the same workbook but for it to ignore the sheet name?

Basically I have a workbook with the first sheet looks up info on all the other sheets. The 1st row looks up info on the 2nd sheet, 2nd row looks up the 3rd sheet etc.
The existing formula is literally [ ='unique name of second sheet'!A1 ]

The problem is that I need to make several workbooks each with a compilation sheet and every individual workbook has sheets which are named differently so the farmulae on each row of each compilation sheet has to be updated every time.

Surely Excel refers to each tab with some default name/number which is more fundamental to the actual name of the sheet given by the user?
I imagine that the formula I'm after would be something like:
[ ='sheet2'!A1 ]

The good thing is that the info returned on each sheet is in the same cell every time (a1), and the order of the sheets in the compilation doesn't matter (I just go consecutively).

Any insight into such a feature would be great. If a Macro exists I can post on the other side

Thanks!
gj

cool, I'll give it a go thanks!