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.