Hello again,
The excel file EC_Techniques for Dynamic Charts has given me some ideas to apply to my job as a Customer Service for two automotive customers. I want to create a dynamic chart with a combobox. I want to select the date in the combobox (in tab "Montly Performance View") and that the chart tells me how many parts of each type of part (FRONT RH, FRONT LH, REAR RH, etc) we have sold in that month. The problem is that the format of the date in the combobox seems not to be the same as that in the tab "Montly Performance Data". Is there a way to fix this?
I attach the file so that you can see what I mean.
Thanks in advance,
Cristina
Hi Cristina,
I looked at your attached. Great work on the dynamic charts at the top of sheet 'Monthly_Performance_View'!
. . . Just to be picky, you might improve cell E26 to be =IFERROR(AVERAGE(CloseRange_Carsets),"") to avoid those nasty #DIV/0 results. And same for the other result cells . . .
I tried to work with your combo box, however it seems to be unstable, and the combo box is not selecting data correctly from the source table (e.g. it sometimes reverts to 2019 where it should be 2020). Not sure if this is your version of Excel, or something else.
However I changed the control to a List Box, which is not quite as beautiful as a Combo Box, but seems to be more stable. I then created 2 ranges in the table on the data sheet: StartTable and MonthRow. This is in the attached workbook. The resulting chart pulls the correct numbers, so I'll leave it to you to format as you need.
Cheers,
Harold
Hi Christina,
In Monthly_Performance_View!C34 you have text, and in "Monthly_Performance_Data" sheet you have dates formatted to be displayed as "mmm-yy". No matter how the cell is formatted, the content of the cell is still a number: 43617 is the number that corresponds to cell D7 from "Monthly_Performance_Data" sheet.
The MATCH function is sensitive at data format, if you search for a text string in a column with numbers, there will be no match. Same if you search for a number in a column where cell format is text. (those cells usually have a warning saying that there is a number stored as text)
You can use =TEXT("1/6/2019","mmm yyyy") In Monthly_Performance_View!C34, but for the MATCH function to work, you have to convert C34 to date, to match the Monthly_Performance_Data!$D$5:$D$25 format:
C39 formula: =MATCH(DATEVALUE($C$34),Monthly_Performance_Data!$D$5:$D$25,0)
DATEVALUE($C$34) returns 43617 for 01/06/2019
Hello Harold,
I couldn't open your file but Catalin's answer was really useful in order to solve the problem with dates in combobox. Now, the only problem with this chart is that I wanted each type of part in a different color as in Nov-19. I arranged the colors as in Nov-19 for every single month but when I close the document and open it again, this has dissapeared. If there is a solution for this I would appreciate your help if not, it is no problem, the chart is ok for me too.
Thanks a lot to you both for your support. I am really enjoying this training course.
Best,
Cristina
Hi Cristina, I'm not sure why you couldn't open the workbook I sent you. I have converted it here to .xlsx so please try to open this one (not sure why yours needs to be .xlsm as there is no macro code used). I think there is some problem with the combo box you are using to select dates. When I tried the combo box on your workbook it would not select dates correctly and consistently, & when the month was changed (especially to a date after Dec 19), kept returning the message: Maybe consider using a list box to select the month, as suggested in my previous response - it seems to work consistently in the attached workbook and preserves the different colored bars in the chart after the workbook is closed and re-opened. Glad you're enjoying the course . . it's obvious that you have learned LOTS! Cheers, Harold
Thanks Harold, I will try 😉
Cristina
