Forum Discussion

Dgosse's avatar
Dgosse
Copper Contributor
Apr 05, 2023
Solved

Sales plans workbook

Hello!  This is a little tough to explain exactly what I want here, but we will see how it goes. This workbook has 2 main purposes: A place to keep yearly plans for each customer, and then to be ab...
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Apr 06, 2023

    Hi Dgosse 

     

    2023 CROP Plans
    - Each Customer "block" consists of 15 rows followed by 1 empty row
    - Deleted empty rows between Customer blocks. Keeping them is not a problem but will cause you a challenge when adding new Customers
    - Formatted data as Table
    - Hidded the Table Header

     

    CROP Plans Sorted
    - Output of Get & Transform query CustomersSorted where Customers are sorted A-Z
    - Table header is hidden
    - Conditional format. rule every 16 rows

     

    Customers by Plan
    - Output of Get & Transform query CustomersByPlan

     

    How To use
    When you add/change data in 2023 CROP Plans, go to Excel Data (tab) > Refresh All ==> CROP Plans Sorted & Customers by Plan will update

     

    Any question let me know

Lorenzo's avatar
Lorenzo
Silver Contributor
Apr 06, 2023

Hi Dgosse 

 

(The setup of your yearly plans sheet isn't ideal and prevents i.e Sorting as you expect + other analysis)

 

Q1: NO due to all the empty cells in Column A below each Customer name. An option probably exists with Power Query (aka Get & Transform) if you're fine getting a new sheet where the data are sorted

 

Q2: Probably doable (automatically) with Power Query as well

 

Questions:
#1 Version of Excel (2016, 2019...365)?
#2 OS/Platform (Windows, Mac...)?
#3 Can you upload & share a representative workbook (w/o sensitive data) instead of pictures?

  • Dgosse's avatar
    Dgosse
    Copper Contributor
    Apr 06, 2023

    Hello Lorenzo 

    Thank you so much for your reply.

    - Microsoft Office 2021

    - Windows 11 Pro

    - Attached (hopefully) is a sample file that is edited down to 10 sample customers.  I have manually made the plans table so that it functions mostly the way I want it to, so you can see exactly what i'm trying to get to.  

    I am not attached to this layout really at all.  I just want it to function in those 2 main ways I mentioned before.  

    Thank you!

     

    https://www.dropbox.com/scl/fi/p7t8h1hlbpiat8v0xvoo9/Customers-PlansEXPERIMENTS.xlsx?dl=0&rlkey=f1n7hpnzcl5blaazrlorn73f7 

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Apr 06, 2023

      Hi Dgosse 

       

      2023 CROP Plans
      - Each Customer "block" consists of 15 rows followed by 1 empty row
      - Deleted empty rows between Customer blocks. Keeping them is not a problem but will cause you a challenge when adding new Customers
      - Formatted data as Table
      - Hidded the Table Header

       

      CROP Plans Sorted
      - Output of Get & Transform query CustomersSorted where Customers are sorted A-Z
      - Table header is hidden
      - Conditional format. rule every 16 rows

       

      Customers by Plan
      - Output of Get & Transform query CustomersByPlan

       

      How To use
      When you add/change data in 2023 CROP Plans, go to Excel Data (tab) > Refresh All ==> CROP Plans Sorted & Customers by Plan will update

       

      Any question let me know

      • Dgosse's avatar
        Dgosse
        Copper Contributor
        Apr 13, 2023
        It has been a few days since I've been able to get back to this but here I am now.
        This looks great. I will work with it for a while and see.

        Thank you so much!