• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Range on Chart

memot

New Member
Hello,

I have two questions, first one; in my excel i'm getting some values from the excel add-in. When i press the F9 my new dates and some values are written automatically. And some cells must be written manually. After i write my new value manually i must copy the formulas from above, However my charts' ranges must be expanded for my new values. What can i do here?

Please have a look at to my example-set.xls, after that you could understand me better.

My another question is familiar; in my this work i need to find the unique values number everyday and the source is changing day by day. But i don't want to fix my formula every day, maybe some tricks make my formula understand the range automatically.

Please have a look at to my example-check.xlsx.

Best
Mehmet
 

Attachments

  • example-set.xls
    103.5 KB · Views: 6
  • example-check.xlsx
    175.3 KB · Views: 5
For the first question, i guess i could use this solution; i could arrange a large range and unclick the chart option which is "show data in hidden rows and columns". Maybe this could work for my first question. Any suggestions?
 
Have a look at the charts in your file below
Then check out the Named Formula they are based on
 

Attachments

  • example-set-Hui.xls
    84.5 KB · Views: 13
Have a look at the charts in your file below
Then check out the Named Formula they are based on

wow hui you did it, but i couldn't understand how? is there any formula using xDate or XValues? i am very curious really.

By the way hui, you are the expert, can you check out my another file please; example-check.xlsx
 
memot: if you have Excel 2007 or later, then convert your data source to an Excel Table (I use the Ctrl + T shortcut), and Excel will do this for you behind the scenes with no named ranges. Because an Excel Table is basically a collection of dynamic named ranges that Excel maintains on your behalf. Read my article at http://chandoo.org/wp/2014/03/28/tables-pivottables-and-macros-music-to-your-ears/ for more on this.

Hello Jeffrey, thank you very much for your support. But i don't really want to use tables, because from time to time i use another formulas and somehow i am getting problems with tables. When i want to delete the row, table doesn't allow me to etc.. so i was looking for a formulated solution.
 
XDate and XValues etc are Named Formula that establish an array of values
These values can then be used as the source data for the charts
Select a chart,
Right click, Select data
Select a series and Edit
this is where the Named Formulas feed there data to
 
The only time I have trouble with formulas in tables is when using multi cell array formulas, or when using formulas in header rows. There are usually ways around them.

You can delete table rows, but you sometimes have to do it by right clicking in the table and choosing delete row, rather than trying to delete a whole row.

These things are minor inconveniences compared to the benefits of tables. I'd strongly suggest you take a closer look at them, because they were designed for tasks such as this, and if you don't have them in your Excel 'toolkit' than you will be the worse off for it.
 
Back
Top