0

I’m currently working on creating a dynamic chart which is updated monthly in accordance with the information imported into the excel file.

The table is updated with the information, where the chart is supposed to only show the current month based on a specific cell, shown in the attached excel file.

Example text “Period 01JAN18 0001 to 31JAN18 2259”

What the chart needs to do is match the text with a month, so if the text says “JAN” it’s supposed to grab the data for January, if it says “FEB” it’s supposed to grab the data for February.

Currently the chart shows the data for every single month, where I then have to filter out the current month and Year To Date.

Link to excel file

Image of excel file

I hope this explains it properly :)

3
  • First, please remove the "Edit" regarding "offtopic". That concerned your post on Stack Overflow only. Secondly, you are telling what you are currently working on. That's interesting, but Superuser is a question/answer site. What is your question? What do you expect somebody here to tell you? Why do you want to tell us what you are working on? Perhaps you are hoping that somebody does your work for you. No, I guess you actually want to ask about something, so please formulate your post as a question and be specific about the detail you want to ask about. – Tom Brunberg May 9 '18 at 17:17
  • The chnart is supposed to show_ the current month, that would be my question in this case. I could do a TL;DR I need the "Intended Chart" to show the current month based on the period and the year to date data, not showing every single month. – MSL May 9 '18 at 17:24
  • By all means, good luck! – Tom Brunberg May 9 '18 at 17:33
0

The answer below relies on the text in B25 always having 9 characters before the 3-letter name of the current month.

To make the graph dynamic, you can add a new column that always has the current month's data and use that as the y-values for your graph.

This expression: MID(B$25,10,3)

uses MID() to get the 3-letter name of the current month from B25. It starts at position 10 and takes the next 3 letters. So see the caveat above.

Then this expression appends the wildcard character to the month name, and searches for the 3-letter month name (plus anything else) in Row 1: MATCH(MID(B$25,10,3)&"*",B$1:M$1,0) It returns the column number of the month that matches.

Finally, that expression for the column number is used in an INDEX() formula, along with the row number, to get the first data point for the current month. This formula:

=INDEX(B$2:M$21,ROW(O1),MATCH(MID(B$25,10,3)&"*",B$1:M$1,0))

filled down from O1, populates Column O with the data from whatever month is specified in B25. That's September in the screenshot below.

enter image description here

O23 has this formula: =INDEX(B$1:M$1,,MATCH(MID(B$25,10,3)&"*",B$1:M$1,0)) to allow the chart legend to show the name of the current month.

To change the graph, simply select it, and drag the selection box for the y-data over to Column O. Also, use the Source Data dialog to make the Name of the series point to O23.

1
  • Hi, thank you! it worked wonderfully! Had to do some very small modifications but apart from that it was exactly what I was looking for. – MSL May 10 '18 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.