OFFSET function to change Source Data for a Dynamic Chart

[@explorgrande6511]

My goodness, you are the best Excel Tutor! You provide the reasons before proceeding to do it and then showing it patiently step-by-step. Please continue to do more of this for all your Subscribers.

[@Officeinstructor]

Thank you for your motivating comment. Even if you are my only subscriber, I will continue doing it for this very nice feedback

[@explorgrande6511]

Wow, I didn't even expect you'll have time to respond. If I may ask a demonstration please of how you'll handle nominal and ordinal data from 4 different worksheets, then summarize into one Master Sheet, then create a Pivot table and a graph. For example, 77 rows are colored either Green (labeled 'ready'), yellow (almost ready), and red (not ready). Each worksheet contains exactly the same data except for the color choices that vary for each worksheet. Thank you Nabil!

[@Officeinstructor]

Hi, although I do not provide consulting on KZbin but I will give you some helpful tips: 1- What you want to do can be easily done in Power Query. However you can also consolidate multiple lists from multiple worksheets but in this case you need to use the OLD PIVOT TABLE wizard. This wizard can be only brought by using a shortcut: ALT > D > P (ALT D P consecutively). This 3 steps wizard has an option for consolidating multiple sheets. 2- You cannot summarize Data and Analyze it by creating Pivot Tables & Charts based on Fill Color. However you can add a helper column say "Status" which shows the different options: Ready, Almost Ready, Not Ready... then use this column for drag and drop in the Filter area (or Slicer) 3- You could then use the same color in your Pivot Table by applying Conditional Formatting Hope that helps you I invite you to watch my new Tutorial on creating a Dynamic Calendar for Any Month in Any Year, just click here: kzbin.info/www/bejne/q4TIYqKYd7iad8k&lc=z225ivcbevnbifp2iacdp433mk0cifrr1qg0xlk1zl5w03c010c

[@explorgrande6511]

Hi Nabil, I think I can try that. Thank you so much for getting back to me. Keep up the good work!

[@pedrosolorzano6020]

Finally, been searching for a while, not the chart i wanted to make, but information was so perfectly explained that i could put together how to make the chart i wanted.

[@mahamohan1]

Not everyone can be a good teacher like you. Hats off for the wonderful tutorial.

[@Officeinstructor]

Not everyone can be such a Nice and Motivating follower like you

[@SimplyMe2010100]

Great Job Man!!! The best on youtube so far!! Please keep up the great job!!

[@ashoksahu9546]

Excellent video. And your teaching is awesome

[@Everyonelovesyou]

Regards Sir, you have explained this in a best way that a student of low caliber like me has got it.......Thanks

[@sameh1180]

Very nice, Please keep adding your excellent videos on KZbin

[@Officeinstructor]

Thank you Sameh... Please subscribe and share

[@KNLP23]

Very good indeed! You are really good at making complex things look simple. Keep up the good work!

[@Officeinstructor]

Thank you

[@dominiquehandelsman9333]

Thank you. Usually, when using a workbook's scope name-range (DifferentInput), there is no need to prefix the name with the Worksheet's name (ChangeSource!DifferentInput). This is quite unusual.

[@sasavienne]

An *excellent* tutorial. Thanks Nabil. Best regards. Salim

[@khingskaar]

Nice tutorial friend

[@Officeinstructor]

Thank you! Cheers!

[@dhananjaypinjan2643]

Fantastic tutorial.. Very well explained.. Thanks..

[@Officeinstructor]

You are welcome!

[@dashrathpanchal8393]

Hey. Thanks for giving such nice techniques. In addition to this formula I did one more exercise which works great. Im this example height "12" is not dynamic, by COUNTA and OFFSET function i did it dyanamic. Now this entire data for me is dynamic ..if I am adding month and subsequent amount then its updating automatically.. Great idea

[@Officeinstructor]

Fantastic!

[@vijaysahal4556]

great sir amazing sir 👍🏻👍🏻👍🏻👍🏻

[@drimranyusof772]

Very good video to understand offset function and graph modification.

[@Officeinstructor]

Glad you liked it

[@nadermounir8228]

Excellent video. Thank you Mr. Nabil

[@Officeinstructor]

You are welcome

[@chidoziejoseph8579]

I love this piece of work man!!! Great job sir

[@pandharinathjoshi6565]

Great video, ur explanation nice sir

[@Officeinstructor]

Thanks and welcome

[@ImranShaikh-fk4oz]

thankyou very much for your very informative video, very helpful;

[@Officeinstructor]

Glad it was helpful!

[@hemantgusain1260]

Great explanation easy to understand.

[@Officeinstructor]

Glad it was helpful!

[@nonoobott8602]

Great tutorial. Thanks for sharing

[@Officeinstructor]

You’re welcome 😊

[@garciarogerio6327]

Awesome 🤩 👏

[@anandshetty7169]

Can you take this a step ahead using 2 years data together- tell me how to arrange the source data and the offset formula for managing YR1,YR2 filter along with exisiting F1 filter.

[@Officeinstructor]

Yes it can be done with a different layout ... it needs another tutorial not a comment

[@md.ziaurrahman1452]

Assalamu olaikom Nabil Bro, from 🇧🇩 thanks

[@johnnyz7752]

I love your solution, however, is it possible to have the range dynamic and only show the last 6 months in the chart rather than all 12 months?

[@carolshipley7903]

I am using excel version Home Office and Student 2016 and cannot make the Named range work in the series of the chart. Can you please tell me if my version does not work with named ranges in charts?

[@sairay1683]

Awesome sir. While working dynamically the data labels are not displayed for the columns other than online data. Is it possible sir.

[@Officeinstructor]

You are right... some of the formatting is not retained

[@neerajnegi4780]

Hey - Can you show us the line chart for the same data after switching column into rows.vis-a-versa , all the sale channel but only latest 6 months data ? Thanks in Advance

[@starfire787]

Great tuts!

[@Officeinstructor]

Thank you

[@Luciano_mp]

Beautiful trick, thank you!

[@Officeinstructor]

Glad you liked it

[@majidsiddique8227]

Fantastic job

[@majidsiddique8227]

will you explain how formatting applies then change the category?

[@stevennye5075]

Very useful!

[@TausifB]

Wonderful

[@thomasavinash72]

it didnt work for me (excel 2010).followed your instruction . defined name equation also okay my data has months at the top(header)and years as column (left) when i select data the dialog box is not similar to one in the video Pls help unable to attach screen grab pls help

[@ibrahkasule3432]

my online chart data isnt showing after making all data dynamic! others show

[@terryjohnson-skidmore9040]

Hello, I am desperate need of assistance. I have been looking for weeks now for the exact solution. I am working in google sheets- I need to know how to create a chart that will give me rolling totals over the most current 3 month period. I do not know what the best formula for this would be. I have my data source set up and i know how to create pivot tables. I just need to know how to have my reports roll as I enter new data for new dates. Can anyone help me please

[@milburngrimes5070]

Thank you!

[@Udaykaujiya]

Sir very good videos

[@skpradhan2005]

Cracked it, but the chart formatting disappears every time dunno why.

[@Officeinstructor]

I would recommend basic formatting with this volatile function

[@SuperCarparking]

Superb....

[@swarupsantu1987]

when I try to insert the name range "differentinput" by editing chart source data its giving me an error "Excel found a problem with one or more formula references in the worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct." Please help.

[@Officeinstructor]

make sure your Offset function includes the sheet name before the cell reference

[@swarupsantu1987]

@@Officeinstructor I included the sheet name. Followed your instructions step by step. Still showing the error.

[@swarupsantu1987]

@@Officeinstructor Well I tried again from scratch and guess what it worked...thanks

[@venkatasubramanian5721]

Sir I tried, I have two error sir Only work two dropdown only other two is not work what is the problem I fully checked step by step

[@Officeinstructor]

I do not know Venkata which step you missed but the common mistake is if you miss the Sheet name before the cell reference

[@venkatasubramanian5721]

Officeinstructor thanks your reply Occurred error was My careless mistake. Now it’s working fine