Dynamic Chart with Drop-down - Advanced Excel

Change chart using drop-down

[@kishorehs4999]

Super

[@patrickmadziar3860]

Very clever approach. Thank you for sharing with us.

[@TheKenhal]

You are welcome.

[@patrickmadziar3860]

Very clever approach. Thanks for sharing it with us.

[@TheKenhal]

Thanks.

[@christosd1262]

Is there any possible way ti insert the drop down list in the chart?

[@TheKenhal]

No, there is no technical way to include drop down in the chart at present (Jun-20). However we can show it included in the chart after making it in the spreadsheet.

[@noeldoyle2477]

Great lesson, so I have just Subscribed.

[@TheKenhal]

Thanks and welcome...

[@kumaras6912]

Excellent lesson.. it's working :-) Thank you sir :-)

[@TheKenhal]

My pleasure, thanks. 👍🏻

[@TheKenhal]

Great. If the requirement is regular, won't take much time :)

[@cycleSCUBA]

Or insert a pivot chart, then a slicer for each month. Easy.

[@TheKenhal]

Sure, that is another way. Idea before many of our videos is that sometimes some Users want a solution to specific question. You are welcome.

[@frankleopardi5356]

Not sure on how you did the subtracting portion. Once I put in the information it didn't work

[@TheKenhal]

Hi Frank, Consider requirement of HLOOKUP for a row number in the third argument. Since we need first row of data to be displayed, therefore we want 1 as row number in first HLOOKUP. But in the second HLOOKUP below, we want 2 as row number. To get that dynamically, we write ROW() function which gets current row number, and subtract it by a number so we get 1. Therefore, here we write ROW()-4 because we are on 5th row - and this results in 1. Finally when we drag down, then at row 6 it becomes 6-4, resulting in 2nd row...and so on. In future whenever you want to get automatic serial numbers within a function, use this methodology. It will help in generating serial numbers as the formula is copied down. Come back any time. 😊

[@abeshhaque7188]

It is showing that there is no value in the march when i use the formula

[@TheKenhal]

Hi, if possible, please send your file at [e-mail]. Will check and revert.

[@sandhiwijaya6479]

Would you made tutorial if used more than 1 drop down?

[@TheKenhal]

You mean multiple drop down for multiple axis.

[@anuragbawankar685]

Thanks for everything !!! Can I get that drop down cell in dashboard ??? How??

[@TheKenhal]

Thanks for liking it. Yes, you can get that drop-down in Dashboard. If you look at the video again, this drop-down can be made on any sheet. If not clear, please share your Email-ID, or send mail to [e-mail]. We will help you.

[@anuragbawankar685]

@@TheKenhal yeah I got that... Actually what I want is that i have to use that drop-down value on dashboard which I published on web. It's(drop-down) not working. Pls help me on that Email: [e-mail]

[@TheKenhal]

@@anuragbawankar685 Replied your query thru mail.

[@TheKenhal]

Mail sent to your Email-ID ([e-mail]) bounced back, indicating temporary problem. Please share another Email-ID to continue.

[@spydymn5114]

can this be done with dynamic tables as need that for my data keeps getting added at bottom and this wont work correctly if locked to cell range correct?

[@TheKenhal]

First of all Yes. Next, there is no need to change cell addresses from absolute to relative. You can simply apply Data Table to entire table. The moment you add another row, and values (without entering value in formula column), the formula will get updated. And the added row values will appear, also updating the chart. Good to go 👍.

[@spydymn5114]

@@TheKenhal thx for reply will look at that as when i tried to convert all data to table wouldnt allow me as formula not allowed in header of table- will look at it again to see if can get to work thx

[@TheKenhal]

@@spydymn5114 That's only a warning. Just take a backup (as usual), and continue after that warning. It works fine. 👍

[@spydymn5114]

@@TheKenhal thx for reply, I couldnt get to work formula kept getting stripped but got it via another formula using offset and match you can see a 20 sec clip here kzbin.info/www/bejne/lWLMZXZtg8eNjq8 thx again i will re try again your way to see what i am doing wrong

[@TheKenhal]

@@spydymn5114 Thanks, I saw that clip. That only shows a good working of drop-down with chart, but I could not see the formula. However, if you share your Email-ID, I can share that file where I experimented with Data table, with a formula in there. However, OFFSET and MATCH are a good combination for addressing the dynamic ranges. Even INDEX and MATCH can achieve the same. But HLOOKUP and ROW gave me more direct approach, so I applied that.