What are the problems with ref. a large range?

[mike.r.harris]

Does referencing a huge range use more resources for example
does........
=IF(ISNA(LOOKUP(C6,LSM_Summary_ADC!O3:O65000,LSM_Summary_ADC!
F3:F65000)),"x",LOOKUP(C6,LSM_Summary_ADC!O3:O65000,LSM_Summary_ADC!
F3:F65000))
use more resource then say .......
=IF(ISNA(LOOKUP(C6,LSM_Summary_ADC!O3:O100,LSM_Summary_ADC!
F3:F100)),"x",LOOKUP(C6,LSM_Summary_ADC!O3:O100,LSM_Summary_ADC!
F3:F100))
I need to allow for an expanding data range and don't want to keep
extending it.

any help appreciated...... many thanks mike

 

[Don Guillett]

try
On the Summary_ADC sheet>insert>name>define>name it something like
lookuprng>in the refers to box type
=offset($o$3,0,0,counta($O:$O),1)
then refer to that now self-adjusting named range instead

 

[Gord Dibben]

The full column range would take a long time to re-caculate at each calculation.

You would be much better off to create a named dynamic range which expands as
your data range increases.

See Debra Dalgleish's site for dynamic ranges.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP