Hi Domenic,

Thank you very much for reply.

Domenic wrote:
It looks like there are a few problems with the references for your
defined names. The reference for Data needs to be corrected so that a
reference for 51 columns is returned. As it stands, it returns a 51 row
by 1 column reference.

Typo, I missed a comma out in the Refers To box. The range does in fact
reference 51 columns.

Also, the references need to be adjusted to ensure that both arrays
returned by the references have the same number of rows. So change the
references for the defined names as follows...

ID:

=OFFSET('Site Lond'!$A$12,0,0,MATCH(9.99999999999999E+307,'Site
Lond'!$A$12:$A$65536))

When I click on the formula in the Refers To box, the expected number of rows
are referenced. This matches the exact same number of rows as "Data".

Data:

=OFFSET('Site Lond'!$H$12:INDEX('Site
Lond'!$H$12:$H$65536,MATCH(9.99999999999999E+307, 'Site
Lond'!$A$12:$A$65536)),,,,51)

I previously tried the MATCH(9.99999999999999E+307 for the "Data" range but
it did not find my last row of Text.

When I click on the formula in the Refers To box, the expected number of rows
are referenced. This matches the exact same number of rows as "ID".

Will referencing all 65536 rows impact on the speed of the worksheet?

I haven't look further into this, but I think this should solve the
problem. If not, let me know and I'll continue to investigate.

Further assistance appreciated.

Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200710/1