Hi
I have created a "monstrosity" of a formula to return text from another worksheet contingent upon the criteria in column A or D of the 'Transactions for Recode' worksheet and returns text to a maximum of 145 characters.
The formula is way too large and takes some time to calculate. I was hoping that someone could review this formula and assist me to reduce it's size or make it calculate much quicker?
Below is a screenshot of the formula described above.
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(LEN(IF('Transactions for Recode'!B31="Adjustment","Recode - "&'Transactions for Recode'!E31,IF('Transactions for Recode'!O31="",IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!K31&" - "&'Transactions for Recode'!L31),IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!K31))))>25,LEFT(IF('Transactions for Recode'!B31="Adjustment","Recode - "&'Transactions for Recode'!E31,IF('Transactions for Recode'!O31="",IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!K31&" - "&'Transactions for Recode'!L31),IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!K31))),145),IF('Transactions for Recode'!B31="Adjustment","Recode - "&'Transactions for Recode'!E31,IF('Transactions for Recode'!O31="",IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!K31&" - "&'Transactions for Recode'!L31),IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!K31))))
Any assistance would be greatly appreciated.
Regards,
Damo100
[/FONT]
I have created a "monstrosity" of a formula to return text from another worksheet contingent upon the criteria in column A or D of the 'Transactions for Recode' worksheet and returns text to a maximum of 145 characters.
The formula is way too large and takes some time to calculate. I was hoping that someone could review this formula and assist me to reduce it's size or make it calculate much quicker?
Below is a screenshot of the formula described above.
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(LEN(IF('Transactions for Recode'!B31="Adjustment","Recode - "&'Transactions for Recode'!E31,IF('Transactions for Recode'!O31="",IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!K31&" - "&'Transactions for Recode'!L31),IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!K31))))>25,LEFT(IF('Transactions for Recode'!B31="Adjustment","Recode - "&'Transactions for Recode'!E31,IF('Transactions for Recode'!O31="",IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!K31&" - "&'Transactions for Recode'!L31),IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!K31))),145),IF('Transactions for Recode'!B31="Adjustment","Recode - "&'Transactions for Recode'!E31,IF('Transactions for Recode'!O31="",IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!K31&" - "&'Transactions for Recode'!L31),IF('Transactions for Recode'!D31="INVENTORY","Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!M31,"Recode - "&'Transactions for Recode'!P31&" - "&'Transactions for Recode'!N31&" - "&'Transactions for Recode'!K31))))
Any assistance would be greatly appreciated.
Regards,
Damo100
[/FONT]