• 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.

CHART HELP

Hi,

See the file. Named ranges are used to update the chart. Change the chart as per your choice.

Regards,
 

Attachments

  • reddit_556.xlsx
    28.2 KB · Views: 9
Hello,

Much thanks to both of you, I like the version of Somendra Misra a little more. I hope that will do it, otherwise I will post it here :)

Thanks.
 
Hello,

one other thing please:

Because I will have to update the data every month, what do you think is the best practice?
I get the data in the order in the attached file (so now it is filtered from oldest to newest month).

How can we repeat the same thing now? I still want the graph to show me data just for selected name but also to update, after I copy and paste the values for july, august,...?

Thanks in advance!
 

Attachments

  • chandoo_2.xlsx
    12.4 KB · Views: 3
@blazy

Method suggested by @Debraj Dada will be more suitable for such a case, with my method you have to sort it by name and not by month. WIth PT and PC this update will take automatically.

Regards,
 
Okey thanks for the reply. But is there a chance in pivot chart to show me the data just for the selected name in one of the cells (like you did)? Or is the filter only option here?
 
Sorry for misunderstanding and thank you so far.

But because I am creating a dashboard, I want the chart to automatically show me the values for selected name (and not to manualy check it via name filter in the pivot chart)- like you did in the previous file. Is that possible?

And can the scroll bar be added at the bottom if we are using pivot chart (because range of the months is too big)

Thanks
 
Can you please walk me through this code:

=INDEX(Table1[Regular];SMALL(IF($K$1=Table1[Name];ROW(Table1[Name])-ROW($C$2)+1);ROWS(J$4:J4)))
 
@blazy

This is a standard construct to extract data where there are multiples occurrence of same value in the data set. Here, in this case Names are repeated more than once. If it would have been only one entry per name than VLOOKUP would have fetched the purpose. But here say JOHN is repeating many times.

Firstly, I converted your data to Table, for that reason you are seeing Table nomenclature in the formula.

Next start with ROW(Table1[Name])-ROW($C$2)+1 this part will create an array of numbers starting with 1 like {1;2;3;4;5;......}. So next in SMALL function where ever Name selected from drop down is equal to name in the Name column of Table this number is allocated and than ROWS(J$4:J4) will give 1 and when dragged down will create 2,3,4 and so on. So finally, SMALL function will give 1st small, 2nd small and so on as ROW number argument of INDEX function and from array of REGULAR it will give first regular figure, and when dragged down will give 2nd and so on where ever the name is same.

The same is for other functions also.

Hope this will clear your doubt.

Regards,
 
im still a bit confused and cannot get your code to work on my true working file.

1. How can you use the [name] or [regular] on the first table, since this names are refering to the table that do not yet exist (at this point)?

2.) The name range [] is reffering to the offset function right? but again the offset function is used in one step after the first table is created...

hope i make any sense...?
 
Table is not there you need to create one with your source data as in your sample file the source data range is A1:G67. So Select any single cell in your original source and press Ctrl+T, Select the checkbox for my table has headers and press OK. That way your table will be created.

Regards,
 
Back
Top