Forum Discussion
Need help with very complex formula (conditional/if/find/lookup/sum?)
Ashley,
I made the formula almost completely dynamic but it still depends on the order of customers and the order of columns in the "Display" sheets. It also depends on the current layout in sheet "summarytemplate".
=TEXTJOIN(" and ",TRUE,IF(T(INDIRECT("'Display "&{"A";"B";"C";"E";"F";"G";"H"}&"'!J"&MOD(ROW()-1,8)))=SUBSTITUTE(INDEX(B:B,LOOKUP(ROW(),{2;10;18;26}))," Ship",""),"Display "&{"A";"B";"C";"E";"F";"G";"H"},""))I advise you to change your model and put all the data in one "Display" sheet. That will reduce the complexity of the formula.
Ashley,
I made the formula almost completely dynamic but it still depends on the order of customers and the order of columns in the "Display" sheets. It also depends on the current layout in sheet "summarytemplate".
=TEXTJOIN(" and ",TRUE,IF(T(INDIRECT("'Display "&{"A";"B";"C";"E";"F";"G";"H"}&"'!J"&MOD(ROW()-1,8)))=SUBSTITUTE(INDEX(B:B,LOOKUP(ROW(),{2;10;18;26}))," Ship",""),"Display "&{"A";"B";"C";"E";"F";"G";"H"},""))
I advise you to change your model and put all the data in one "Display" sheet. That will reduce the complexity of the formula.
Detlef,
The formula works perfectly!! Thank you for your help!!!!!!
I agree, having the data in one tab could make this process more simple. In the attached document, I have a "summary" tab, which is a shortened version of a tab I work with as well. This tab essentially will hold the years worth of data for every display.
How would I tweak your original formula so that I could pull all my data from the "summary" tab into the "summary template" tab?
Thank you again for all of your help :) :) :) :)
Ashley,
I have no idea what you goal is.
Please provide a workbook with the desired result.
