Hi,

If you expect the range to keep expanding, convert it to a List (Ctrl+L).
Once you convert a range to a List, it expands as data is added by rows.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Huggy" wrote in message
...
Hi Jacob,

The length of the data in the column will change and possibly go past
1000.
Is there a way we can modify the formual to start at A1 and go to the last
cell with data in column a?

Thanks

"Jacob Skaria" wrote:

You are referring to the entire column. Do you really want to refer
A:A..?

Change that to A1:A1000 or to a suitable max limit.

If this post helps click Yes
---------------
Jacob Skaria


"Huggy" wrote:

I have a added number of array formulas in a spreadsheet that has
slowed down
working on the spreadsheet because it recalculates all the array
formulas
each time you enter / change data.

The array formulas are on a separate worksheet to the main data
worksheet.

How do you speed this up or how can you manually recalculate the array
formulas only and allow all other formulas on the main data worksheet
to
re-calculate automatically. I do not want to change auto recalc to
manual
recalc in tools options.

example of one of the array formulas is;

=INDEX(OFFSET(INDIRECT(""&$H$1&"!C$1"),0,0,MATCH(T ODAY(),INDIRECT(""&$H$1&"!$A:$A"),1),1),SMALL(IF(O FFSET(INDIRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),I NDIRECT(""&$H$1&"!$A:$A"),1),1)=$B6,ROW(OFFSET(IND IRECT(""&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(" "&$H$1&"!$A:$A"),1),1))),COUNTIF(OFFSET(INDIRECT(" "&$H$1&"!D$1"),0,0,MATCH(TODAY(),INDIRECT(""&$H$1& "!$A:$A"),1),1),$B6)))

Thanks for the help