Using Index for a dynamic range

I know this sounds like a dumb question but I have really looked all over and I am hoping someone can quickly bring back my sanity.

Hoe do I create a dynamic range for a whole sheet using indirect? Everything I see uses just a range for a column or for rows. I found one that uses a formula that can go down 65536 rows, but Excel 2007 can do many more rows than that. =Sheet1!$A$1:INDEX(Sheet1!$1:$65536,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

I have seen many places that index should be used instead of offset for a dynamic range, but I can't figure out how to get index to look at the whole spreadsheet. Offset was easy, I can tell it to start at A1 and mover over and down using counta. With index I have to tell it the range to look in, but I don't know the range beforehand. I just know that data starts in A1. It could have any number of rows or columns.

That will work. I don't know why that was so hard to find.

Is there a better way to do this formula so that in the future I don't have to go in and change the maximum rows?

Just curious. If you select the whole sheet, how can it be "dynamic". It can't grow by rows or columns.

I don't want to "select" the whole sheet. I just want the formula to look at the whole sheet to determine where the last row and column are.

For now I like what martindwilson suggested. This formula works: =Sheet1!$A$1:INDEX(Sheet1!$1:$1048576,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
I don't like hard coding in the max number of rows, but when I paste a set of data into this spreadsheet, I can just reference the named range and it will always update to include all of the rows and columns in the set of data.

Thank you for your help!