I am working on updating an excel file to make it simpler and more user friendly for users that are not proficient in excel. This spreadsheet is used to track customer returns each year for each customer. The intent is for the chart to show the number of returns for each customer that had returns, but not display the customers that had zero returns.
So, I want to make a dynamic chart that will automatically update when new data is input into the spreadsheet. I created a chart and used the name manager to create names that are used as my data in the chart. Currently the chart works, but only if the customers with blank data (zero returns) fall at the end of the list. If a customer with zero returns is in the middle of the data list, it will plot this customer showing zero returns, but I want it to completely remove this customer from the chart. Also, when a customer with zero returns falls in the middle of the data, it will remove the last customer from the chart.
I attached an example file. There is much more to this file, but I stripped everything out not pertinent to my question and changed names to A, B, C... I want the user to be able to input new data into the "input data" sheet within the blue borders, then have the chart on the following sheet automatically updated. I want customers A through J to always be listed on the "input data" sheet whether they had returns or not, as these are our top customers who often have a return from year to year (but sometimes don't). Then I left some blank columns so the user can add a new customer in case they have a return going into the future. As you can see, customer "D" had zero returns, so I want them to be removed from the chart completely. Also, you will notice that customer "J" was removed from the chart, when they should be showing 2 returns. I believe my issue is with the formula used for the name "Combined", but I haven't been able to wrap my head around it to figure out a correct formula. The formula currently used is:
='Input Data'!$B$48:INDEX('Input Data'!$B$48:$O$48,COUNT('Input Data'!$B$48:$O$48))
If someone could shed some light on this issue for me, it would be very appreciated.
Bookmarks