Thank you for this video Doug. It's exactly what I needed and it is working exactly as expected. Your narration and explanation is easy to follow but even then I made mistakes setting it up and had to step through your video slowly and implement it step-by-step. It put a smile on my face when I finally got it right and the graph changes dynamically according to the start/finish dates. I really appreciate this video, thank you.
Thank you so much! I almost gave up but because of you I can present on the web trading meeting tom! Very informative and clear. I am a fan! Will definitely watch more of ur videos to learn Excel!
Great video, it was just what I was looking for.I have to generate update to a report every week day and this looks so much less grief than mucking about changing the chart values! I have about 20 charts to update.
This was much needed. Thanks a lot. It helped me a lot. I was stuck when I need to make chart according to selection. I was deleting the sheet name also. By the way we can use offset function to make it dynamic.
Excellent resource, how can I take the live changing data to next row and create a line chart? OR Is it possible to create live line chart with live data
that would have to be refreshed automagically. If it is somethin where you are manually updating on a recurring basis, incorporate the table feature th-cam.com/video/Knk_MD_eOpc/w-d-xo.html
Thanks so much for this video. Before I've tried to link the chart with the range but stuck at the edit data. Now I got it that I have to press F3 to change the range. ✌🏻thanks and btw really looooove your accent!
I'm having issues with this - help would be appreciated. Running Excel 2010 so not sure if that's an issue. I've created the range with the index formula in, which saves ok. However, when I edit the graph and change the axis to Sheet1!rangename it gives the "We found a problem with one or more formula references in this worksheet" error. On one occasion I got it to accept (don't believe I changed anything), but as soon as I pressed ok, I got the error again and the chart axis showed nothing. Is there another guide on this that you were referring to (maybe in text) so I can try and suss where I'm going wrong ?
Hello Doug - I'm enjoying and learning much from your videos. How is the best way for me to get an A to Z of your charting videos? From basic to most complex types of charts.
Hey, great video. What if i wanted something more specific. Based on Month and Days. For ex. The ability to. Use the drop down to select the month. Then another drop down for the date
Check if your date is a text string or value. Also check if it’s the same between the lookup values since dates are displayed in forms like mm/dd/yy but excel sees them as decimal numbers
you might want to see these vids for some insight - > th-cam.com/video/lCsvSdk4Tro/w-d-xo.html or use pivot table with the fields that allow filter options so you don't have to write these functions th-cam.com/video/ZQxgYtl33xY/w-d-xo.html
seems like there is some value in the actual cell...a formula? a blank cell shouldn't have drawn the line out...try using the NA() function in the cell.
Great info! I can get this working just fine but I have trouble converting it to VBA code. Any tips on how to get VBA to reference the named ranges for y-values and x-values?
Is there a way to create a dynamic chart without named ranges and vba? Where the data is pasted into the worksheet might vary from time to time, so I would like to try to account for the possibility that it may not always appear on the same line or column. Thanks!
I followed the steps. On changing the chart range to the defined names, I get error pop up - 'a formula in this worksheet contains one or more invalid references. Any solution to that?
I keep thinking I'm getting this, but at work we recently upgraded to Office 365. I think that is interfering with what I'm trying to do. For example, when I created the Index:Index formula, it actually spills the results in that cell and those below. And because I had already formatted my data as a table (much larger dataset, about 500 columns and eight columns of data, looking for a multi line chart) I think that's affecting some of what I am trying to do. Have you run this on 365?
Ah, I finally figured out what I was doing wrong. For my case, it worked better when I created the banned ranges with the score et to the Worksheet level rather than Workbook. Then when I used the names ranges in the chart, I didn’t get a formula error.
Hi, I have seen videos to populate pivot based on the value selection in the drop down using Index and Match function. My data is tricky not able to replicate the same issue. Below are the columns of info I have in the spread sheet:Dept Month Target Actual Difference Mktg Jan-18 100 80 20 Mktg Feb-18 120 118 2 Mktg Mar-18 90 94 4 TeleM Jan-18 200 210 10 TeleM Feb-18 150 148 2 TeleM Mar-18 110 103 7I want my chart to change dynamically. Could you please help how to write the Index Formula. I want to display the chart data by for selected period by dept. I will select the dept and then the range of the periods which will display the chart. Since I have multiple times dept and month are coming in the data spreadsheet it is not giving the right result.Could you please help?
Maybe consider using slicers. Create a pivot chart from the pivot table and then use slicers to pick and choose. See my slicer playlist for some insights th-cam.com/video/4XdzSSoS-bM/w-d-xo.html
Doug H do you think the results from the formula index and match are confusing? The outcome from match is actually an index I.E. 5, 8 etc. This is called index in data science languages like Python or R however Excel call it with the function match. When I don't use these formulas for some time I always forget that the index is called with the match function and not with the index function
I guess it can be confusing when trying to use terminology across different applications! In R, I think it's called an data frame, but in SQL it's called a table...go figure!
Why is TH-cam Jamming Political Ads down our throat? And if they are going to start doing that, then perhaps the Fair Doctrine needs to be applied, with Donald Trump getting equal air time.
I found a mistake in this video. I executed the same for my case. It was all great, yet I prescribe you to test the outcome once again by keeping the END DATE consistent and changing the START DATE. You will see that the values are not changing in the chart when you do likewise! If you don't mind take a stab at amending it, and give the modifications that I must inculcate!
The commentary with the small mistakes and all was very natural and helped me better understand what was going on. Many thanks for this video
Hi Dominic Ng, glad you liked it, thanks for commenting!
Thank you for this video Doug. It's exactly what I needed and it is working exactly as expected. Your narration and explanation is easy to follow but even then I made mistakes setting it up and had to step through your video slowly and implement it step-by-step. It put a smile on my face when I finally got it right and the graph changes dynamically according to the start/finish dates. I really appreciate this video, thank you.
Hi KiwiJohn, glad you liked it, thanks for commenting!
Thank you, Doug!
You’re welcome!
Thank you so much! I almost gave up but because of you I can present on the web trading meeting tom! Very informative and clear. I am a fan! Will definitely watch more of ur videos to learn Excel!
Hi Catherine Pan, glad you liked it, thanks for commenting!
Nice work, your help with these charts is making me look smart at work!
Hi Cal_Hennesey, thanks for the comment!
great tutorial..very helpful and visually appealing. Thanks and hope to see many videos as such, especially on dashboard
Hi GIRISH KUMAR, glad you liked it, thanks for commenting!
Great video.
Maybe adding dynamic to the ranges, period, budget and actual:
offset($B$8,,,Count($B:$B),1) for the period.
Arne Munther I second you. that would be really convenient.
Hi Arne Munther, thanks for the comment!
Nice video. Very informative and clearly explained 😊 Thank you 😊🙏🏿
Glad it was helpful!
Can you do this for vertical axis?
Great video, it was just what I was looking for.I have to generate update to a report every week day and this looks so much less grief than mucking about changing the chart values! I have about 20 charts to update.
So glad it helped you! Thanks for the comment!
This was much needed. Thanks a lot. It helped me a lot. I was stuck when I need to make chart according to selection. I was deleting the sheet name also. By the way we can use offset function to make it dynamic.
Hi Kiran Kapruwan, thanks for the comment!
Excellent resource, how can I take the live changing data to next row and create a line chart? OR
Is it possible to create live line chart with live data
that would have to be refreshed automagically. If it is somethin where you are manually updating on a recurring basis, incorporate the table feature th-cam.com/video/Knk_MD_eOpc/w-d-xo.html
Thanks so much for this video. Before I've tried to link the chart with the range but stuck at the edit data. Now I got it that I have to press F3 to change the range. ✌🏻thanks and btw really looooove your accent!
Hi C Z, glad you liked it, thanks for commenting!😄
I'm having issues with this - help would be appreciated. Running Excel 2010 so not sure if that's an issue.
I've created the range with the index formula in, which saves ok. However, when I edit the graph and change the axis to Sheet1!rangename it gives the "We found a problem with one or more formula references in this worksheet" error. On one occasion I got it to accept (don't believe I changed anything), but as soon as I pressed ok, I got the error again and the chart axis showed nothing. Is there another guide on this that you were referring to (maybe in text) so I can try and suss where I'm going wrong ?
Hi Janson Smith - Amazon FBA, try a post on the mrexcel.com forum!
Way to go Doug !
Hello Doug - I'm enjoying and learning much from your videos. How is the best way for me to get an A to Z of your charting videos? From basic to most complex types of charts.
Sorry....I don't have a playlist for my charting videos, it's separated by Excel versions.
th-cam.com/users/dough517playlists
Hey, great video. What if i wanted something more specific. Based on Month and Days. For ex. The ability to. Use the drop down to select the month. Then another drop down for the date
For interactivity with date, maybe consider using the timeline slicer. This video might give some idea
th-cam.com/video/kgRRCYNCKis/w-d-xo.html
How do you do this for a date rather than period? Excel keeps giving me an error
Check if your date is a text string or value. Also check if it’s the same between the lookup values since dates are displayed in forms like mm/dd/yy but excel sees them as decimal numbers
Is there a way to skip changing formulas per each new data entry
have the data use the table feature would help th-cam.com/video/Knk_MD_eOpc/w-d-xo.html
How would you do this if you wanted to select a period and either budget or actual and have the corresponding number populate a cell?
you might want to see these vids for some insight - > th-cam.com/video/lCsvSdk4Tro/w-d-xo.html
or use pivot table with the fields that allow filter options so you don't have to write these functions
th-cam.com/video/ZQxgYtl33xY/w-d-xo.html
Hello Doug, what if the actual is only up to a certain month? Line drops to zero, how can this be avoided? Thank you, great and helpful videos!
seems like there is some value in the actual cell...a formula? a blank cell shouldn't have drawn the line out...try using the NA() function in the cell.
Thanks, Doug!
If I required to add new row each day then everytime I need to chage range of period or is there any other way??
may want to incorporate the usage of the table feature. For some insight see th-cam.com/video/Knk_MD_eOpc/w-d-xo.html
Excellent
Thank you so much 😀
Great trick!
great tutorial
Thanks Dennis Ryan!
Great info! I can get this working just fine but I have trouble converting it to VBA code. Any tips on how to get VBA to reference the named ranges for y-values and x-values?
sorry don't know VBA well
Is there a way to create a dynamic chart without named ranges and vba? Where the data is pasted into the worksheet might vary from time to time, so I would like to try to account for the possibility that it may not always appear on the same line or column. Thanks!
might want to think of MSFT Power BI. Some interesting charts/graph th-cam.com/video/cHJdLv1nt-A/w-d-xo.html
thank you very much..quite useful!
I followed the steps. On changing the chart range to the defined names, I get error pop up - 'a formula in this worksheet contains one or more invalid references. Any solution to that?
may need to check the names ranges to ensure it points to the required areas
I keep thinking I'm getting this, but at work we recently upgraded to Office 365. I think that is interfering with what I'm trying to do. For example, when I created the Index:Index formula, it actually spills the results in that cell and those below. And because I had already formatted my data as a table (much larger dataset, about 500 columns and eight columns of data, looking for a multi line chart) I think that's affecting some of what I am trying to do. Have you run this on 365?
If the table feature has been used, then formulas created will usually spill down to adjacent cells.
Ah, I finally figured out what I was doing wrong. For my case, it worked better when I created the banned ranges with the score et to the Worksheet level rather than Workbook. Then when I used the names ranges in the chart, I didn’t get a formula error.
thank you very much..quite useful! :)
Hi Ladda Ongard, glad you liked it, thanks for commenting!
Hi,
I have seen videos to populate pivot based on the value selection in the drop down using Index and Match function. My data is tricky not able to replicate the same issue. Below are the columns of info I have in the spread sheet:Dept Month Target Actual Difference
Mktg Jan-18 100 80 20
Mktg Feb-18 120 118 2
Mktg Mar-18 90 94 4
TeleM Jan-18 200 210 10
TeleM Feb-18 150 148 2
TeleM Mar-18 110 103 7I want my chart to change dynamically. Could you please help how to write the Index Formula. I want to display the chart data by for selected period by dept. I will select the dept and then the range of the periods which will display the chart. Since I have multiple times dept and month are coming in the data spreadsheet it is not giving the right result.Could you please help?
Maybe consider using slicers. Create a pivot chart from the pivot table and then use slicers to pick and choose. See my slicer playlist for some insights th-cam.com/video/4XdzSSoS-bM/w-d-xo.html
Doug H do you think the results from the formula index and match are confusing? The outcome from match is actually an index I.E. 5, 8 etc. This is called index in data science languages like Python or R however Excel call it with the function match.
When I don't use these formulas for some time I always forget that the index is called with the match function and not with the index function
I guess it can be confusing when trying to use terminology across different applications! In R, I think it's called an data frame, but in SQL it's called a table...go figure!
Youre a g. Thanks
You're Welcome!
it doesnt work for me and is giving me an error
Nader Mounir, that is very interesting...thanks for letting me know.
I Just spent a night trying to get this to work when the period dynamically increases.
Hi Thales Maciel, try a post on the mrexcel.com forum!
Wow !
Hi Aravind M, thanks for the comment!
Why is TH-cam Jamming Political Ads down our throat? And if they are going to start doing that, then perhaps the Fair Doctrine needs to be applied, with Donald Trump getting equal air time.
Hi Joel Lanier, thanks for the comment
I found a mistake in this video. I executed the same for my case. It was all great, yet I prescribe you to test the outcome once again by keeping the END DATE consistent and changing the START DATE. You will see that the values are not changing in the chart when you do likewise! If you don't mind take a stab at amending it, and give the modifications that I must inculcate!
I am having the same issue. Whats the solution?
himanshu arora, that is very interesting...thanks for letting me know.