Creating a flexible Chart for Dashboard - Chart knows when there is nothing in X axis

I am working on creating a dashboard template in excel for our business program reporting and am running into a small frustration. We can have anywhere from 3-7 metrics, and I am building a relational workbook that updates multiple sheets based on input on one sheet. When I am making charts I am setting them up for the 7 rows that could be filled with metrics, but when there are <7 metrics, the chart has multiple blank areas on the X axis.

Is there a way to make the number of items on the x axis conditional based upon something being entered in the cell? Does this make sense?

Hi NScarritt:

Yes, this is possible.

The best approach will be to use dynamic range names. You can look this up on the internet. Here is one example:

http://support.microsoft.com/kb/183446

Another approach would be to force the irrelevant data to show as #N/A. Excel ignores this data for chart purposes. This is especially helpful when you don't want a line chart to dive to the X axis at the bottom.


Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If we have been of assistance, please let us know. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Can someone explain to me what is going on in this formula, referenced in David Coop's link? The KB post doesn't do a great job of explaining why this formula is used or how its formed.

Thank you! Reps for help

In the Refers to box, type =OFFSET($B$2,0,0,COUNTA($B:$B)-1), and then click OK.

Happy New Year, hope you had a good break.

The formula you posted is designed to be in the Refers to box in a range name. It is saying, the range name refers to a range, starting at $B$2 for the number of values in the column. The COUNTA of Column B tells you the number of non-blank cells. The -1 is because you want to stop at the last cell, and because the data has a heading in B1.

Note: This assumes that there are no blanks in the column.

Is this any help?

It is not clear in your original post, but I assume that when the metrics vary between 3 and 7, the cells are consecutive.

Let me propose an example.

Your chart data for your dashboard is in $A$1:$A$7. Give this range a Range Name (using Name Manager), say Info and in the Refers To box, enter =OFFSET($A$1,0,0,COUNTA($A1:$A7)). Note: This is a variation on the formula you posted. I have assumed there is no heading, so there is no need for the -1, and instead of referring to the entire row, I have only referred to the 7 rows where the metrics will be.

In your chart, instead of referring to $A$1:$A$7, refer to [B]Info[B].

I hope this helps!

Regards,

David


Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

{EDIT} Double post on accident. Deleted.

Thank you for the response, and sorry for the delayed response. It is good to know that the #N/A won't show in charts, however, in this case since this is for a dashboard I have an if statement to return blank if there is no data/title.

I'll look into the dynamic ranges... Thank you!

Your assumption was correct. I currently have my Metric titles in C6-C14. The data extends a few cells over to the right. I have set my range name as 'Metrics' , and using name manager I have set the chart series to:

=OFFSET('Q2 Dashboard'!$C$6,0,0,COUNTC('Q2 Dashboard'!$C6:$C14))

Now the series titles are blank and below where they would be it just says 'Metric'. The series also don't auto adjust... have I missed something in the logic?

Originally Posted by nscarritt

Your assumption was correct. I currently have my Metric titles in C6-C14. The data extends a few cells over to the right. I have set my range name as 'Metrics' , and using name manager I have set the chart series to:

=OFFSET('Q2 Dashboard'!$C$6,0,0,COUNTC('Q2 Dashboard'!$C6:$C14))

Now the series titles are blank and below where they would be it just says 'Metric'. The series also don't auto adjust... have I missed something in the logic?

There is one thing that I see that is wrong! COUNTA is an Excel function! (It means count alpha, or something like that). You have changed it to COUNTC. (I understand why). Change this back to COUNTA, and see how you go.

If it doesn't work, post an example, and I will have a look.

Regards,

David

Hm... I'm having trouble getting it to work still. I'm not seeing a way to load up the spreadsheet to the forum. What do you think the best way to go about it is?

To post an example, - Click on Go Advanced and click on the Paper Clip.

Very sorry for the delay. Here is the 'functionality test' piece of the dashboard structure. I haven't began creating our program specific ones yet.101.xlsx

Hi nscarritt,

I have attached your file.

There have been some changes on your to be hidden sheet (L:M) so that I could do some counting for named ranges.

I have created ranges called Metrics, Q1under and Q1over. These are used in the chart which should now be "dynamic".

Please note that it is important that you don't have blank rows in your charted data - this will cause unexpected results.

Hope this helps

David

nscarritt - 101.xlsx


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Sorry this is so late! Thank you so much.