Dynamic charts

Will you please attach a sample Excel workbook? Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Change the named range FROM the format

=OFFSET(Sheet1!$b$2,0,0,COUNTA(Sheet1!$b:$b)-1)

TO the format:

=OFFSET(Sheet1!$B$2,0,0,SUMPRODUCT(--(LEN(Sheet1!$B$2:$B$100)>0)))

Here's a sample...

Post a sample chart, as requested at post 2.

OK. Try this out for size. select your countries from the Dropdown menu in column A.

There's a lot going on here. So, if you want me to explain any of the bits, let me know.

You haven't really explained what you want to do, so I can't answer properly. Short answer: try it and see...

1. (Modified a bit). I turned your raw data into a Table, and called it Data, by selecting the range and INSERT/Table and renaming it in the box near the top left of the screen. The references to the table will extend automatically as/when you add new rows. Now, if you enter CTRl-F3, it'll take you to the Name Manager. there are two. One is "Data" - the raw data table. the other is "List". It uses a dynamic formula

=Data!$P$2:INDEX(Data!$P$2:$P$100,COUNTA(Data!$P$2:$P$100))

which will extend down column P until ir reaches the last entry. Currently (as you can see) it goes down to row 100.

I used data validation to restrict entries in column A only to items in List (quicker, prevents mis-spelling errors). select any/all of the light pink cells in A2-A7. Data/data validation/data validation. the "source" box tells excl where to look for the acceptable data.


2. The lookup formula in the beige cells (B2 to J7) returns #N/A when it doesn't find a match. Excel ignores #N/A when plotting charts. However, having a pile of #N/A errors looks ugly. So, i used conditional formatting to make the #N/A text the same beige colour as the cell fill. So they become invisible. Use Home/conditional formatting/manage rules in any of the beige cells to see what was done there. When you select a new country in A, the formula finds a match, returns and plots the data.

The only way I could think of to create the legend was to restrict the number of countries per graph to a maximum number. i chose 6. I then created 6 text boxes, each one referencing A2, A3, etc, down to A7. I formatted the text COLOUR in those boxes to match the COLOUR of the apprpriate dataset. If no country is selected, the text box remains blank. Once an entry is made in A2, the first text box populates and is automatically formatted to match the dark red colour of the first dataset. Once set up, I just aligned and spaced out the text boxes to look uniform. You can add more lines to allow for more graphs, it will need you to spend some time repeating /extending what I've done; but the legend will very quickly get very complicated and messy.

3. You can, but if (as now) the list is totally dynamic: there's no need to.

Can you use VBA. Yes. But not with me.

Or it would cause a problem? Yes. Not a HUGE problem, but yes, it will casue a problem. Try it yourself and see what happens.

Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.