Log In
Register
Home
Active Member
May 25, 2021
Offline
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.
Trusted Members
February 13, 2021
Offline
Hi Nick!
You're dynamic named range formula is working perfectly, the problem comes with the count. The formula counts the number of non-blank cells (9) then counts from B48 9 columns, unfortunately it is counting column E in your return count so column E is shown in your graph instead of Column K. Have you thought about a pivot graph instead? You can set it to hide the information that have zeros. Something like this shown.
Trusted Members
February 13, 2021
Offline
I'm sorry, I'm having fun with this challenge. After a bit of finagling, I have come up with a formula to correct the issue in your last formula. The caveat is I don't know if you could avoid a 0 on all your static customers (Customer D in your example) unless someone else knows something I don't (which is extremely likely). But the formula I came up with is adding that middle blank back in by counting the blank cells in your combined column minus the empty cells in your customer column.
='Input Data'!$B$48:INDEX('Input Data'!$B$48:$O$48,COUNT('Input Data'!$B$48:$O$48)+(COUNTBLANK('Input Data'!$B$48:$O$48)-COUNTBLANK('Input Data'!$B$7:$O$7)))
The red is added on to your existing index formula. Hope either one of these options helps, or gets you closer to your goal. I'm sorry I don't know how to eliminate the empty customers from the graph. 🙂
Jessica
Active Member
May 25, 2021
Offline
Hi Jessica, thanks for both responses. These are definitely getting me closer. I don't have much experience with pivot charts, but I will look into them further, it looks like they may be pretty useful. The pivot chart you set up in your first response seems to show what I am looking to show, except it doesn't seem to update the chart automatically when I change the input data. Is there a way to automatically refresh/update the chart when data is changed?
And I like the formula you added in your second response, this works very well and still keeps the function of dynamically updating the chart as data is adjusted. Like you said, the only caveat is it wont remove the customers with zero values. But this is so close, maybe I'll just have to live with the zero value customers still showing on the chart. I'll play around with it some more.
Active Member
May 25, 2021
Offline
Another idea I have been thinking about is ranking the customers. The core customers don't necessarily need to be in a specific order. If I create another table that is fed from the input data table that is ranked so the high return values are first and zeros are at the end, then that would avoid the zeros in the middle that are giving me issues. Then I could feed the chart from the ranked table. I have been playing around with RANK and HLOOKUP functions, but have been having problems getting HLOOKUP to work the way I thought it would.
Trusted Members
February 13, 2021
Offline
Unfortunately the caveat of the pivot chart is that it does have to be manually updated by selecting the chart>analyze tab>refresh to get it to refresh. There is a way to refresh it automatically using VBA when source data has been changed but I'm not very knowledgeable about that. I guess there are pros and cons to every possible solution....
Pivot Tables can be very intimidating when you first get into them, but once you get into it they aren't bad and very useful. When I first started them I had to try to find excuses to use them so I could get them figured out, now I no longer need to find an excuse I just say "Hey, a pivot table would be great here!"
Personally, if you try the ranking option, I would try Index/Match instead of HLookup, just because of the layout of your data. I do, however, have a chart I have set up for ranks for one of my dashboards. Let me know if you would like to sneak a peak at it and I can attach it for you. 
Active Member
May 25, 2021
Offline
Jessica, thanks for your help. Index/Match worked perfectly with my data ranked. My chart now works great and updates dynamically and excludes customers with zero returns. Problem solved.
My next task is to figure out a simple user friendly way for the user to select only certain months to display in the chart, but I will start a separate post if I cant figure out a way to do it. Thanks again.
1 Guest(s)
