Need help with very complex formula (conditional/if/find/lookup/sum?)

[Ashley Tanay]

Hello excel Gurus, 

I'm looking for help with writing a formula in a current workbook. 

I need a formula that will look up information in multiple tabs, and return one or multiple values into another tab. 

I have created a smaller version of my workbook I need help with, for exercise purposes so I could post here on the forum. 

What I am looking for:

In "Summary Template" tab, I need the name of "Display A, Display B, Display C, etc., " to show up in the corresponding month (column B, or column E, or column H) also in the corresponding Customers  row (Customer 1, 2, 3,...)

So for instance:

I have Display A

Customer 1 ship month is January

Display B

Customer 1 ship month is also January

I would need a formula that could automatically fill in, column B, row 3 with "Display A and Display B"

To tell me that Customer 1 has two different displays shipping in the Month of January.

I would be manually entering in the ship months on each Display tab (please note for formula purposes, the ship months are volatile and I may be changing them more than once - if that matters?- )

Thank you for any help with guiding me in the right direction of how to tackle this!!!

[#2 · Accepted solution]

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.

[#3]

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  :) :) :) :) 

[#4]

Ashley,

I have no idea what you goal is.

Please provide a workbook with the desired result.