I would - this would solve all my problems:) But the formula resides in
a column next to a query from a database that automatically expands and
retracts as it is refreshed. This creates the problem of requiring
'catch-alls' to account for every situation. I can blank out certain
cells or tell the formula to not calculate (like the if="1"), but there
does have to be something there.

Ive removed all my indexes and starting from scratch with some entirely
too simple if statements to create vllookup ranges, hopefully thatll
work

Bob Phillips wrote:
Why don't you totally remove the formula in rows 5:15, 17:29, etc. and only
have them where required?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"S Davis" wrote in message
oups.com...
Yeah, I can see that does speed things up a bit. However, the data this
is running from is from a DS7 database, which outputs everything as
text. In fact, in my own formula I have to use =if($D4="1"... or else
it won't work.

I appear to have hit some sort of barrier with the number of formulas
in this sheet. Calculation time is absolutely absurd, and I'm at a
point where I can not drag down or even copy new formulas into cells.
I'm not sure how I'm going to go about this. Might be splitting this
into two seperate workbooks with references to each other :(
-Sean


Bob Phillips wrote:
It seems to speed it up, presumably (as I guessed you thought it might)
because 11 out of 12 rows it will take the default action.

This seems to be much faster, put TRUE in D4, D16, etc, and use


IF(D4,INDEX(Last_Inspection_Reported,MATCH(1,(Last _Inspection_Bus=$A4)*(Last
_Inspection_ID="CV"),0)),0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"S Davis" wrote in message
ups.com...
Hi there,

Right now I'm using this formula:



=INDEX(Last_Inspection_Reported,MATCH(1,(Last_Insp ection_Bus=$A4)*(Last_Insp
ection_ID="CV"),0))

This is repeated down a list quite a long ways. I only really need
this
calculation every 12th time to avoid duplicate data, but I can handle
leaving it as is if it will slow down the calculation time.

My question is this: If I change my formula to:



=If($D4=1,INDEX(Last_Inspection_Reported,MATCH(1,( Last_Inspection_Bus=$A4)*(
Last_Inspection_ID="CV"),0)),"")

Will this speed up or slow down the calculation time? Column D
contains
a repeating series of numbers from 1-12, with each new series
containing a new dataset; thus I only need the results once, BUT this
is operating from a Query/named range, so the formula needs to be in
there for future expansion/retraction of the data, so its not possible
to just enter the formula only on the rows containing a "1" in column
D.

Thanks for your opinions,
-Sean