[I had to repost as my original message from 3 days ago crashed]
Hello Experts,
This is an effort to speed things up. I am using 248 out of a possible
256 columns, and 150 rows down...and it's slow. Each cell in my
"Master" tab has this long formula, starting from D4 down and across.
It compares the "Current" tab with the "Previous" tab and produces a
result. Is there a way to convert this formula into code? and have it
in every cell, from D4 to IN150, so that it'll run faster?
This formula is from "Master" tab, cell [D4]:
=IF(ISBLANK(A4),"",
IF(OFFSET(CurrentBase,MATCH($A4,CurrentUnitList,0) ,MATCH(D$1,CurrentDate
Header,0))IF(ISNA(OFFSET(PreviousBase,MATCH($A4,P reviousUnitList,0),MAT
CH(D$1,PreviousDateHeader,0))),0,(OFFSET(PreviousB ase,MATCH($A4,Previous
UnitList,0),MATCH(D$1,PreviousDateHeader,0)))),"Y" ,
IF(OFFSET(CurrentBase,MATCH($A4,CurrentUnitList,0) ,MATCH(D$1,CurrentDate
Header,0))<IF(ISNA(OFFSET(PreviousBase,MATCH($A4,P reviousUnitList,0),MAT
CH(D$1,PreviousDateHeader,0))),0,(OFFSET(PreviousB ase,MATCH($A4,Previous
UnitList,0),MATCH(D$1,PreviousDateHeader,0)))),"X" ,"")))
Formula Interpretation:
CurrentBase is cell A1 of the "Current" tab
CurrentUnitList is cell A2:A150 of the "Current" tab
CurrentDateHeader is cell B1:IN1 of the "Current" tab
PreviousBase is cell A1 of the "Previous" tab
PreviousUnitList is cell A2:A150 of the "Previous" tab
PreviousDateHeader is cell B1:IN1 of the "Previous" tab
In the "Master" tab,
If [A4] is blank, then leave the cell empty.
If not;
Perform a double lookup by row [A4] and by column [D1] as the search
criteria.
Go to "Current" tab and get the intersect.
Go to "Previous" tab and get the intersect.
If the "Previous" intersect is [NAerror], then "0". Then, carry on with
the next comparison calculation.
If the "Current" intersect is "Previous" intersect then the result is
"Y".
If the "Current" intersect is < "Previous" intersect then the result is
"X".
If it's neither or <, then "" [blank cell].
I'm in need of a code in place of this formula.
Thank-you in advance.
Ricky
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!