Display selected item from a list for input and calculations


I  developed an module displaying Rep, Customer and product.


This required to enable planning to be done at Rep Level, Customer and Product levels.  This will be used to input unit volumes per product / customer and Rep


Revenue display sheet.JPG

The easy way would be to have the total product list but each Rep are responsible for selected product and of course the customers has their preference as well.


For that i developed a matrix depicting those relationships.

Rep Cust Product Mapping.JPG 

Now the problem statement:

 Referencing to the picture below where i use the Rep, product and customer lists resulting in this comprehensive module. How can i limit the view in alignment with the requirements above

Revenue Calc sheet.JPG







  • Hi,


    I would encourage you to do the following:

    Screen Shot 2019-05-14 at 11.28.44 PM.pngScreen Shot 2019-05-14 at 11.28.50 PM.png


    However if you're keen with the 'Rep_Customer_Product' list, i would ask why you have the 'Rep' by 'Customer' by 'Product' by 'Time' module? Why not just create a 'Rep_Customer_Product' by 'Time' module? 


    Having said that, you can still achieve what you wanted by creating the 2 line items below:

    1. Concatenated Name = NAME(REP) & "_" & NAME(CUSTOMER) & "-" & NAME(PRODUCT)

    2. Active? = ISNOTBLANK(FINDITEM(Rep_Customer_Product,Concatenated Name))


    Finally create a Filter by referring to 'Active?'




  • @Roelofg 

    As per current best practice 



    You should create System modules for Reps, Customers and Products

    You should then split the text string into 3 sections in each module:


    Rep Details.Rep code to use = NAME(ITEM(REP)) & “_”
    Customer Details.Customer code to use NAME(ITEM(CUSTOMER)) & “-”

    Product Details.Name = NAME(ITEM(Product))

    Concatenated Name = Rep Details.Rep code to use & Customer Details.Customer code to use & Product Details.Name


    This is much more efficient


  • Sheethal
    I would suggest you to go for a design which involves Numbered List with the combination of Reps, Customer & Product. This will help you to avoid sparsity as not all the Products will be sold to all customers by all Reps and this in turn will help you to keep the model size at optimum level.
  • Roelofg

    Thank you for all the contributions... a lot to absorb for me...

  • Roelofg

    The thinking behind the detail stems from a need to assign responsibilities across cost center, regions, customers and products...