Forum Discussion
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 able to sort those customers by their plans.
This first picture is the sheet that contains the specific plans for each customer. There are several categories of plans that each customer may or may not have (Seed, spring dry fertilizer, starter fertilizer, etc.). My first question is about this sheet.
Is there a way that I can sort this sheet by Column A (customer name), where all of the plans will follow the customer? That way I can search through this sheet easier. This sheet will change regularly as add customers in the future.
My second question is regarding this table. The purpose of this table is to find the customers name, and tell me if they have any plans for each category. The problem with the way I have made this table is that I have to make it almost completely manually. I cant figure out any formulas that will search my "plans sheet" automatically to get me this information.
Any ideas for how I could do this better?
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 HeaderCROP Plans Sorted
- Output of Get & Transform query CustomersSorted where Customers are sorted A-Z
- Table header is hidden
- Conditional format. rule every 16 rowsCustomers by Plan
- Output of Get & Transform query CustomersByPlanHow 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 updateAny question let me know
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?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!
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 HeaderCROP Plans Sorted
- Output of Get & Transform query CustomersSorted where Customers are sorted A-Z
- Table header is hidden
- Conditional format. rule every 16 rowsCustomers by Plan
- Output of Get & Transform query CustomersByPlanHow 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 updateAny question let me know
