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

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.