Beautiful plan to use named ranges in tatters....excel crashes with error report

[theta]

I have 3 named ranges. When I try to index them all fine...except ParameterTable. As soon as I type =INDEX(ParameterTable, .... excel unexpectedly stops with send error report rubbish etc

Here are the ranges :

ParameterClients =Parameters!$B$1:INDEX(Parameters!$1:$1,0,MAX((Parameters!$1:$1<>"")*(COLUMN(Parameters!$1:$1))))


ParameterFields =Parameters!$A$2:INDEX(Parameters!$A:$A,MAX((Parameters!$A:$A<>"")*(ROW(Parameters!$A:$A)),0))


ParameterTable =Parameters!$A$1:INDIRECT(ADDRESS(MAX(ROW(ParameterFields)),MAX(COLUMN(ParameterClients))))


When I explore using Name Manager, all fine, can select ranges etc no issues.

 

[theta]

Although if I changes ParameterTable and omit the first ref Parameters!$A$1...I can then index it using the fomula :

=INDEX($A$1:ParameterTable, ...

?

 

[theta]

Ah...think I have spotted the issue...not using 'Parameters'!

Fixed this but it still doesn't work!

 

[theta]

Here is a link to the file :

http://www.sendspace.com/file/hn198a

Using the Name Manager, everything explores correctly...

But =INDEX(TABLE, bombs out

 

[RoryA]

Which version of Excel are you using?
And why not just convert the range to an actual table?

 

[theta]

Using Excel 2007

I will be using the table approach in the future (like the referencing method) but this project going to stick with dynamic

Does the workbook function correctly for you?

 

[RoryA]

No it doesn't - looks like a bug. However, you can use:
=OFFSET(HEADERS,1,0,COUNTA(FIELDS))
as the range for TABLE.

 

[theta]

Thanks RORYA, will try this method

Never like using COUNTA incase some idiot puts data in a weird place leaving gaps

Sheet protection time!

 

[RoryA]

Then why not use a table...

 

[theta]

I'll get back to you when I think of a good reason

No you are right, I will be converting all of my sheets to tables at some point, then re-format colours etc to keep the appearance the same

And get on-top of the Table referencing method.

Thanks for your help!