Dynamic range not working in pivot table

[Mr930R]

I set up a dynamic range as follows:
=Data!$A$1:INDEX(Data!$A$1:$G$1000000,MATCH(9.99E+307,Data!$A$1:$G$1000000))

Supposedly this should work as source data for a pivot table but it says "invalid reference" when trying to set up.
This is Excel 2010 but supposedly worked since 2003.

Any ideas/help?

thanks
Fred Emmerich