|Are you "copying" these formulas or dragging them to AutoFill?|
If you are dragging them down (or over), you could use a combination of the INDIRECT and ROW (or COLUMN) functions.
You can read up on these functions in the Excel Help files, but in general the INDIRECT function is used when you want to use text in a formula to represent a cell reference and the ROW and COLUMN functions simply return the Row or Column number in which the function resides.
For example, if you put =ROW() anywhere in Row 5, it will return 5. If you put =COLUMN() anywhere in Column C it will return 3.
Since these functions return numbers, you can perform operations on them.
e.g. if you out in this formula in Sheet1!A1, it will be equivalent to =Sheet2!B3
=INDIRECT("SHEET" & ROW()+1 & "!B3")
ROW() will return 1
ROW()+1 will return 2
"SHEET" & ROW()+1 & "!B3" will return the text string Sheet2!B3
The INDIRECT function will use that text string and return the value in Sheet2!B3
If you drag this down, the ROW() function will increment with each row, thereby incrementing only the Sheet number, since the rest of the text will remain constant.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.