Charting a Dynamic Year-to-Date Period Using the OFFSET Function in Microsoft Excel (Part 1 of 2) скачать в хорошем качестве

Charting a Dynamic Year-to-Date Period Using the OFFSET Function in Microsoft Excel (Part 1 of 2)

In the last video I showed you a dynamic chart with rolling 6 months, 12 months, or a variable end date selected by the user from a drop-down list. In this video you will see, how to make a dynamic chart with the year-to-date period using the OFFSET function. The solution does not need additional tables or Visual Basic for Applications and is instead based on using dynamic named ranges and formulas returning arrays. A year-to-date is the period from the beginning of the year to the current date. The formulas will be shown for the calendar year and a fiscal year that is different from it. In this example the fiscal year will start in April. The OFFSET function will recalculate every time that a change is made on the sheet, even in cells not related to it, so in part 2 I will show you an alternative for large files using the index function. 00:00 Dynamic year-to-date period 01:00 Preparing the table and chart 01:36 Formula for calendar year-to-date period using the OFFSET function 04:15 Defining the dynamic named range for values 04:55 Defining the dynamic named range for axis labels 05:26 Using the dynamic named ranges in the chart 06:36 Formula for fiscal year-to-date period using the OFFSET function 08:55 Dynamic named range for values 09:13 Dynamic named range for axis labels 09:44 Choosing the ending date from a drop-down list 10:00 Preparing the drop-down list using data validation 10:44 Adapting the formula for calendar year-to-date 12:18 Using the formula in the Name Manager 14:06 Adapting the formula for fiscal year-to-date 14:55 Using the formula in the Name Manager 15:38 Troubleshooting The link to the last video with the rolling months:    • Excel Chart with Rolling 6 Months, 12 Mont...   The link to part 2 with the index function:    • Charting a Dynamic Year-to-Date Period Usi...   For more contents like this, please subscribe to my channel. #MsExcel #ExcelTips Screenshots used with permission from Microsoft.