Thank you, Doug!

@DougHExcel

4 жыл бұрын

You’re welcome!

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

@DougHExcel

7 жыл бұрын

Hi Dominic Ng, 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.

@sangamwadhwa4051

7 жыл бұрын

Arne Munther I second you. that would be really convenient.

@DougHExcel

3 жыл бұрын

Hi Arne Munther, thanks for the comment!

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 ?

@DougHExcel

3 жыл бұрын

Hi Janson Smith - Amazon FBA, try a post on the mrexcel.com forum!

Nice work, your help with these charts is making me look smart at work!

@DougHExcel

3 жыл бұрын

Hi Cal_Hennesey, thanks for the comment!

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.

@DougHExcel

7 жыл бұрын

Hi KiwiJohn, glad you liked it, thanks for commenting!

great tutorial..very helpful and visually appealing. Thanks and hope to see many videos as such, especially on dashboard

@DougHExcel

7 жыл бұрын

Hi GIRISH KUMAR, glad you liked it, thanks for commenting!

Nice video. Very informative and clearly explained 😊 Thank you 😊🙏🏿

@DougHExcel

3 жыл бұрын

Glad it was helpful!

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!

@DougHExcel

6 жыл бұрын

Hi Catherine Pan, glad you liked it, thanks for commenting!

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.

@DougHExcel

8 жыл бұрын

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.

@DougHExcel

5 жыл бұрын

Hi Kiran Kapruwan, thanks for the comment!

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!

@DougHExcel

6 жыл бұрын

Hi C Z, glad you liked it, thanks for commenting!😄

Great trick!

Excellent

@DougHExcel

3 жыл бұрын

Thank you so much 😀

thank you very much..quite useful!

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.

@DougHExcel

8 жыл бұрын

Sorry....I don't have a playlist for my charting videos, it's separated by Excel versions. kzread.infoplaylists

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

@DougHExcel

4 жыл бұрын

that would have to be refreshed automagically. If it is somethin where you are manually updating on a recurring basis, incorporate the table feature kzread.info/dash/bejne/faKfwa99j8i1oMY.html

thank you very much..quite useful! :)

@DougHExcel

7 жыл бұрын

Hi Ladda Ongard, glad you liked it, thanks for commenting!

great tutorial

@DougHExcel

7 жыл бұрын

Thanks Dennis Ryan!

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

@DougHExcel

3 жыл бұрын

For interactivity with date, maybe consider using the timeline slicer. This video might give some idea kzread.info/dash/bejne/nZuGtKWSfqaxmdY.html

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?

@DougHExcel

3 жыл бұрын

sorry don't know VBA well

Youre a g. Thanks

@DougHExcel

6 жыл бұрын

You're Welcome!

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!

@DougHExcel

6 жыл бұрын

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.

@yoliedepaz3340

6 жыл бұрын

Thanks, Doug!

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?

@DougHExcel

6 жыл бұрын

you might want to see these vids for some insight - > kzread.info/dash/bejne/nnen2LWdm5e6otI.html or use pivot table with the fields that allow filter options so you don't have to write these functions kzread.info/dash/bejne/jIWsybutnJaZqLw.html

Is there a way to skip changing formulas per each new data entry

@DougHExcel

4 жыл бұрын

have the data use the table feature would help kzread.info/dash/bejne/faKfwa99j8i1oMY.html

If I required to add new row each day then everytime I need to chage range of period or is there any other way??

@DougHExcel

4 жыл бұрын

may want to incorporate the usage of the table feature. For some insight see kzread.info/dash/bejne/faKfwa99j8i1oMY.html

Wow !

@DougHExcel

3 жыл бұрын

Hi Aravind M, thanks for the comment!

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

@DougHExcel

7 жыл бұрын

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!

How do you do this for a date rather than period? Excel keeps giving me an error

@DougHExcel

2 жыл бұрын

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 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!

@DougHExcel

2 жыл бұрын

might want to think of MSFT Power BI. Some interesting charts/graph kzread.info/dash/bejne/lXx-xq6vYdHaXaQ.html

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?

@DougHExcel

7 жыл бұрын

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?

@DougHExcel

3 жыл бұрын

If the table feature has been used, then formulas created will usually spill down to adjacent cells.

@kilroyscarnivalfl

3 жыл бұрын

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.

I Just spent a night trying to get this to work when the period dynamically increases.

@DougHExcel

3 жыл бұрын

Hi Thales Maciel, try a post on the mrexcel.com forum!

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?

@DougHExcel

6 жыл бұрын

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 kzread.info/dash/bejne/ZoyY3LWMn7aTkrA.html

it doesnt work for me and is giving me an error

@DougHExcel

4 жыл бұрын

Nader Mounir, that is very interesting...thanks for letting me know.

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!

@macroshiv

7 жыл бұрын

I am having the same issue. Whats the solution?

@DougHExcel

3 жыл бұрын

himanshu arora, that is very interesting...thanks for letting me know.

Why is KZread 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.

@DougHExcel

4 жыл бұрын

Hi Joel Lanier, thanks for the comment