Hello,

I have this formula and it's very long. Below is a copy of it

=IF(J_Z_1=CODECOL,INDEX(FINAL_ALL,MATCH(A2,'Final Merge Fields'!A:A,0),MATCH
("Code",FINAL_1,0)),"")

The only part of that whole entire formula that changes as I drag it down is
where it says A2. That should change to A3, A4, and so on.

I have to drag this formula down 50,000 rows in 2 worksheets. Is there
anyway I can possibly condense it by like just putting it in one cell and
telling all the other cells to get it from it except to update the A2,A3 part?


Your help is greatly appreciated.

Derek

--
Message posted via http://www.officekb.com

By the way I know this forumla takes forever to calculate, but thats fine
because everytime I use this sheet I will only have to have it calculate once
so thats not a problem. The problem lies in that the sheet will become well
over 100Mb when i'm done with it if i have to drag that formula all over

--
Message posted via http://www.officekb.com

and if this helps at all

J_Z_1 refers to cell W1 on sheet "J-Z"

CODECOL refers to cell A6 on sheet "Single Data"

Final_all refers to 1:65536 on the sheet "Final Merge"

Final_1 refers to 1:1 on sheet "final Merge"





Derek Y wrote:
Hello,

I have this formula and it's very long. Below is a copy of it

=IF(J_Z_1=CODECOL,INDEX(FINAL_ALL,MATCH(A2,'Fin al Merge Fields'!A:A,0),MATCH
("Code",FINAL_1,0)),"")

The only part of that whole entire formula that changes as I drag it down is
where it says A2. That should change to A3, A4, and so on.

I have to drag this formula down 50,000 rows in 2 worksheets. Is there
anyway I can possibly condense it by like just putting it in one cell and
telling all the other cells to get it from it except to update the A2,A3 part?

Your help is greatly appreciated.

Derek

--
Message posted via http://www.officekb.com

It looks like you could first define two cells that have the formulas for the
IF condition and the second match.
Ex: =(J_Z_1 = CODECOL). Define that cell reference as Valid.
And =match("Code",FINAL_1,0). Define that cell as ColNum.
Then your function code be modestly simplified as
=if(Valid,index(final_all,match(...),ColNum),"").
I *think* that you're using FinalMergeFields interchangably with FinalMerge,
in which case this simplies a bit further:
=if(Valid,vlookup(a2,FinalMergeFields,ColNum,0),"" ).
HTH. --Bruce


"Derek Y via OfficeKB.com" wrote:

and if this helps at all

J_Z_1 refers to cell W1 on sheet "J-Z"

CODECOL refers to cell A6 on sheet "Single Data"

Final_all refers to 1:65536 on the sheet "Final Merge"

Final_1 refers to 1:1 on sheet "final Merge"





Derek Y wrote:
Hello,

I have this formula and it's very long. Below is a copy of it

=IF(J_Z_1=CODECOL,INDEX(FINAL_ALL,MATCH(A2,'Fin al Merge Fields'!A:A,0),MATCH
("Code",FINAL_1,0)),"")

The only part of that whole entire formula that changes as I drag it down is
where it says A2. That should change to A3, A4, and so on.

I have to drag this formula down 50,000 rows in 2 worksheets. Is there
anyway I can possibly condense it by like just putting it in one cell and
telling all the other cells to get it from it except to update the A2,A3 part?

Your help is greatly appreciated.

Derek

--
Message posted via http://www.officekb.com

Bruce, That was a huge help.

You were right in that I am using Final merge fields interchangably. The
seperate sheet is actually 'Final Merge Fields'.

I got those definitions to work just fine and it made it a bit shorter, but
your final simplification where you are using VLookup instead, I could not
get to work. All I did was this so far:

=MATCH("Code",FINAL_1,0) defined as ColNum
='CITY (A-I)'!$V$1='SINGLE DATA'!$A$6 defined as Valid

replaced those two area's of my original formula with those words.

When I make your final replacement it wont even accept it as a formula. I
guess I dont know what
FinalMergeFields is supposed to be referring to in your formula or how to
define that.

Thanks SO much for your help.

Derek



bpeltzer wrote:
It looks like you could first define two cells that have the formulas for the
IF condition and the second match.
Ex: =(J_Z_1 = CODECOL). Define that cell reference as Valid.
And =match("Code",FINAL_1,0). Define that cell as ColNum.
Then your function code be modestly simplified as
=if(Valid,index(final_all,match(...),ColNum),"" ).
I *think* that you're using FinalMergeFields interchangably with FinalMerge,
in which case this simplies a bit further:
=if(Valid,vlookup(a2,FinalMergeFields,ColNum,0)," ").
HTH. --Bruce

and if this helps at all

[quoted text clipped - 23 lines]

Derek

--
Message posted via http://www.officekb.com