If you mean a new sheet in the same workbook, this works for me in Excel 2003, it shows the last value in column A in Sheet1 as long as there are no intermediate blank cells in column A.
=OFFSET('Sheet1'!A1,COUNTA('Sheet1'!A:A)-1,0)
You can see how we can make a formula reference cells in a different sheet by using the sheet name and an exclamation mark, like this =Sheet1!A2
You don't need single quotes around the sheet name if the sheet name has no spaces, but I use them anyway out of habit.
Example
=OFFSET('Sales Figures'!A1,COUNTA('Sales Figures'!A:A)-1,0)
