Formula to concatenate text based on criteria and < than 145 characters

[Damo100]

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]

 

[AlanY]

what's the criteria in simple English?

 

[Damo100]

Hi AlanY

If the reference in column B of the 'Transactions for Recode' worksheet = 'Adjustment', concatenate several different cells
If the reference in column O = "" and the reference in column D = "Inventory", then concatenate a number of different cells
OR if the reference in column O = something other than zero and

the reference in column D = "Inventory", then concatenate a different set of cells
All of this within the context of returning a maximum of up to 145 characters in total.

Hope that makes sense?

Regards,
Damo100
​

 

[AlanY]

what I would do is to make use of helper columns to concatenate the cells based on the criteria and use the if() function to pick it out.
btw, what are you going to do if it's > 145 characters?