The simplest format would be to have a formulae that extended across the range: And then we would just change the formulae every month. The example here is a set of data that is generated by an automated system and downloaded each month. Please check your email. • The width of our dynamic range – which is 3 in this case. Let's see how it works with this basic example. Growing list of Excel Formula examples (and detailed descriptions) for common Excel tasks. His focus is on helping corporate professionals visually communicate the messages in their data so they don’t overwhelm and confuse executives. To define the data to be used in the chart, we need to create two named ranges. We have a list of orders for each customer. So here the criteria and criteria_range both are variable. Disclaimer: Some of the links in my newsletter or on my website may be affiliate links where I am paid a small commission for recommending a product or service that I personally use and trust. Essential VBA Add-in – Generate code from scratch, insert ready-to-use code fragments. The OFFSET function uses 5 parameters to return a reference to a range that is offset from the original range. • 0 is to ensure an exact match, Now we can specify the size of our dynamic range by the OFFSET function which has 5 arguments: In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. All books, products and seminars are independent publications and are not affiliated with, nor have they been authorized, sponsored, or otherwise approved by Microsoft Corporation. Contact me to learn more. where "start" is the named range G4, and "offset" is the named range G5. Concatenate the text with the month selected: Cell Q3 = “Revenue Comparison for “&Q4. Practice Excel functions and formulas with our 100% free practice worksheets! In both cases as more data is added, the chart uses the new data to expand the chart. The first named range is for the names of the quarters that will be the x-axis labels. So for the month of March the range is 3 columns long, and for the month of June it would be 6 months along. To create a dynamic chart first we need to create a dynamic range in excel. You can jump to the steps using this link. If your need is to dynamically update your sum when you add new data, the easiest solution is to insert your data in a Table. I am trying to create a Dynamic Range for my charts with a data completed based on formulas. Thanks for subscribing! 3. To make it easier to use the named ranges when creating the charts, I suggest you use meaningful names, such as Week2X and Week2Values for the different named ranges. Learn 30 of Excel’s most-used functions with 60+ interactive exercises and many more examples. To find this starting cell we use the INDEX and MATCH functions. Having formulas select the data to be included in the chart. Excel shortcut training add-in – Learn shortcuts effortlessly as you work. Similar to defining the dynamic named ranges above, we need to set up two named ranges for each chart, one for the category axis labels (the dates) and one for the data values (the production levels). = SUM(OFFSET(c5,0,0,MATCH(c8,$c$3:$j$3,0),1)). Click Developer > Insert > Combo Box (Form Control). In cell I2 we will choose the month. He has written nine books and over 100 articles on the topic of effective presentations and his ideas have appeared in publications around the world. See screenshot: 2. Defining dynamic named ranges for multiple charts. This article shows the table method as well as another method that involves using the OFFSET function to define the set of cells to be used in the chart. Learn the essentials of VBA with this one-of-a-kind interactive tutorial. To do this, you have to make a cell reference containing the month. Using a dynamic drop down list is a big time saver. 28,29,30,31 as applicable) =OFFSET(A2,0,1,DAY(DATE(YEAR(A2),MONTH(A2)+1,1)-1)) will give you the range to the right of … Each month can have up to five weeks and the days that fall in each of the first through fifth week of the month will change each month. Formulas are the key to getting things done in Excel. In the Name Manager on the Formula ribbon, we define a range named ChartX using the following formula. Now I would like to have some kind of dropdown in tab 2 where I can choose the month. The only one thing that leads you to use next method is when you delete data from a table, your chart … You have the option of adding a dynamic chart header. Means, all the cells in the data shows values based on a formula. The fifth argument of the OFFSET function is the width, in number of columns, of the range. We have used static range where value cells are fixed and here with Dynamic range, our range will change as well add the data. #3 – A dialog box pops up. • As there are less formulae changes, less scope for error The data contains one record for each day of the month reporting production for the day. The approach is to have a formula indicate which days fall in each week during the month and then the dynamic range method above will create the chart based on the data that is in that week. With our two named ranges defined (ChartX and ChartValues), we can create a chart using these two named ranges. To create a dynamic date list, you can use a formula that increments a start date to create and display additional dates. = OFFSET(c5,0,0,MATCH(c8,$c$3:$j$3,0),1), Finally we need to tell Excel to SUM this to give the complete formulae as: He regularly presents highly rated sessions at national and regional conferences of financial professionals. A standard chart in Excel uses a defined set of cells for the category axis and the data values. If you need to add new entries in you source list continually, you must have to use a dynamic drop down list. For example, "Next Week" represents a different range of dates, every day that you open the pivot table file. Here is the simple data table. The method described below handles these situations and any situation where you select consecutive data points in a list of data. I will not recommend any product or service that I do not stand behind, and any compensation that I may receive is minimal and will not influence my recommendations. As an example, I will use the situation where the analyst wants to select which quarters appear in the chart. Please check your entries and try again. Now if we change the month in the dropdown, the correct year to date figure flows through: As this is an automatic update this approach has the following advantages: ©2005-2021 Dave Paradi - PrivacyPolicy: We will never sell or distribute your e-mail or information to anyone. We use the same formula, changing the column number in the INDEX function to 2 to define the ChartValues named range. Note that the method works only if the selected data points are consecutive, with no gaps. • Reference is the top left hand corner of our dynamic range – cell C5 – the first cell that we want to sum In the formula example above, the changes would be: You can see that this method is flexible and can accommodate many different situations simply by changing a few parameters in the formula used to define the named ranges. Using named ranges to define the data to be charted. I hope this tip will help you to get better in Excel but now you need to tell me one thing. • C3:J3 is the address of all our months Two requests I recently had led me to develop this method. The COUNTIFS Function should look for the month column that will be criteria range. Make sure that the cells are consecutive so that the method works properly. How to make dynamic drop down list in Excel This example demonstrates the general approach to creating a cascading drop down list in Excel by using the new dynamic array functions . And, the best part is, it’s a one-time setup, you don’t need to apply it again and again. And I want a dynamic list based on all rows with apple in it. Excel Offset: Dynamic Range for Moving Average Calculations (10:42) Another good example is the case where you allow the user to select the month and your formula provides the average of the next 3 months. We can extend this idea to having a formula select the data to be included in the chart. "At Automate Excel we offer a range of free resources, software, training, and consulting to help you Excel at Excel. We could set up a dynamic range whose size varied on the month that we are in. Contact: Dave@ThinkOutsideTheSlide.com or Call (905) 510-4911 In tab 1 all the data is stored in column C,D,E and F. Column G till L will be calculated based on the data from C,D,E and F. In tab 2 some graphs will be made (in this case from 27th of March till 12th of April). The size of the range is governed by the month. • This is the height of our dynamic range which is 1, So our OFFSET formulae is: In above chart, when I have added the amount for Jun, chart get updated automatically. The main function is the OFFSET function that returns a reference to a range of cells. So I have a list of expenses along with their date and I want to create a field that will sum them up automatically based on month. For example, you want it to say, “Revenue Comparison for [month]”. You need to create a Form Controls Combo Box in advance. SALES REPORT — DYNAMIC HEADER. As we change the month in the drop down then the size of the range changes. The above mentioned article on creating a simple dynamic range chart has a good step-by-step explanation of using named ranges to create a chart. 100+ VBA code examples, including detailed walkthroughs of common VBA tasks. Then draw a Combo Box in cell A1. I like to just have the first worksheet as the presentation layer — let’s name it Dashboard — and the second worksheets as the data layer — let’s call that Data. #2 – In the Insert tab, click on Tables under the tables section. Excel VBA Consulting – Get Help & Hire an Expert. • Rows – the number of rows down from our base cell – this is 0 You would then use each pair of named ranges to create the five charts as described above and in this section of the referenced article. Easily insert advanced charts. In the second request, someone needs to graph results for each of the five weeks in a month but the days included in each week changes each month. But what if you want to create a chart where the data range gets bigger or smaller based on criteria? Remember to include the sheet name when using the named ranges in defining the chart, just as the sheet name is included in the formula above. New posts Search forums. The row number for the starting point is selected using the MATCH function. Sign up here to receive notices. For a chart I would like a dynamic range so that I get a chart with data I need from the start of the table (4 jan 2016) but also would like a chart (from the same table) with data only from the previous month, this month, last week and the past 5 weeks. To make a dynamic chart, you first have to define some dynamic range names. All you have to do, convert your normal range into a table (use shortcut key Ctrl + T ) & then use that table to create a chart. ... Excel Questions . *Uploaded file with data* Hello, I'm looking for a way to create a dynamic chart range. The organization wants to view each week (starting on a Monday) in a separate chart. This is 1 since we only want one column in the range. Dynamic Date Range. Hence we now want to work out the year to date for March. The range includes Column C so the selection can be based on the “Y” values the user has entered in this column. For now I have a dynamic range called Each month can have up to five weeks and the days that fall in each of the first through fifth week of the month will change each month. Method 2: Ctrl + Shift + Right Arrow/Down Arrow. See the syntax or click the function for an in-depth tutorial. • Cols – the number of cols across from our base call – this is 0 This article describes a method to create a dynamic defined name. To calculate this we use the COUNTIF function. Then we can use the week1 through week5 columns (E through I) to calculate if that date is in the specific week, placing a “Y” in the cell if it is part of that week and leaving the cell blank if it is not. Inserting your data into a table is very useful because your formulas refer to a named range and not to the references of cells. However as we wish the range to vary by month we will put our MATCH formulae here The typical dynamic range chart advice is to use a table where the chart expands when more data is added to the table. But here, instead of using th… List of 100+ most-used Excel Functions. But that chart wouldn't be dynamic, and you'd be stuck performing monthly maintenance on it. I’m using your tutorials (which have been very helpful!) The INDEX function returns a cell reference from an array based on selection criteria. And the count should change too. So for the month of March the range is 3 columns long, and for the month of June it would be 6 months along. To change the values used in the chart, just change the cells in Column C that contain a “Y” value. The first helper column is the week number. ... Hi I tried the formula to pull in agent names criteria based on supervisor and month. =OFFSET(reference, rows, cols, height, width), Where: In the example above the user selected the data for the chart by entering a “Y” in Column C for the data points to be charted. Microsoft, PowerPoint, Windows and other terms are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Using Dynamic Ranges – Year to Date Values. Excel Table feature was introduced in Excel 2007 version of Windows and if you’re versions prior to it, you won’t be able to use it (see the next section on creating dynamic chart range using formulas). This basic approach is one of the core components in the dashboards I work on every day, and it can be applied to a much more rob… The formula checks if the week number for this day is one more than the week number for the first day of the month. I want the user to select the "name" in a cell and specify the months in another set of cells i.e. We could set up a dynamic range whose size varied on the month that we are in. For example, for week 2, the array would be $F2:$F32. The second request I mentioned above is an example of where we can use formulas to select the data for the graph and then use the dynamic range method to create the chart. In the formula above, a reference array defined larger than the current range of cells has been used in order to account for future quarters and their results. Create a list with all month names. For this, in the first step, we need to change the data into Table format from the Insert menu tab. We can add a drop down like so: So that we can specify the current month. The size of the range is governed by the month. Since we know that the “Y”s are in consecutive rows, this will be the number of rows we want in the chart. 1.Firstly, you should copy and paste the data to backup original data. Microsoft product screen shot(s) reprinted with permission from Microsoft Corporation. As our data has headers so remember to check on the box “My Table has headers” and click ok. #4 – Our Dynamic Range is created. That chart is based on a set range of 8 cells, which have colour names, and total sales for those colours in the selected date range. Required by Amazon: As an Amazon Associate I earn from qualifying purchases. List of 200+ Excel shortcuts. The first argument for the OFFSET function is the starting cell for the range to be returned. For example, if he selected March 1st to March 31st as the date range, there would be 31 days of data, with multiple tests per day. Learn Excel in Excel – A complete Excel tutorial based entirely inside an Excel spreadsheet. Presenting Financial Information Visually in PowerPoint, Latest Annoying PowerPoint Survey Results, The State of Financial Presentations Survey Results, Proportional Object Collection Calculator, Solving Presentation/ PowerPoint Problems, Customized Excel Chart Training Workshops, You can jump to the steps using this link, Purchase a Tutorial video, Excel sample file, and article PDF. We create the formula SUM to calculate the sum of the orders. Normally you'd select the data and make a chart using the chart wizard. In this formula, you have two COUNTA functions to get the last non-empty row and last non-empty column (row_num and column_num arguments of the INDEX function, respectively).In the array argument, you feed the entire worksheet (1048576 rows in Excel 2016 - 2007; 65535 rows in Excel 2003 and lower).. And now, let's define one more dynamic range for our data set: the range named sales … 2.Then select the date range and click Kutools > Format > Apply Date Formatting, see screenshot:. As an example, the formula is cell F4 is =IF(D4=$D$2+1,"Y",""). Menu. In cell I2 we will choose the model. Then it will look for model in that months column. This is a complex formula that uses a number of functions. The approach is to have a formula indicate which days fall in each week during the month and then the dynamic range method above will create the chart based on the data that is in that week. ©  2021 Spreadsheet Boot Camp LLC. See screenshot: These values can be will change. • The spreadsheet can be used by somebody who has limited Excel knowledge – they can just change the dropdown and not be bothered by formulae. Of course we can extend this to multiple data series of values but we will keep this example simple. to build a few dynamic lists in excel from a dataset based on one or two criteria. It just gets updated automatically when we add new cells or rows. Dynamic charts where you select the data to be included. In the example shown, the formula in B5 is: = start + ROWS( $B$5:B5) - 1 - offset. Define the Dynamic Names. Excel Formula Training. The MATCH function finds the first row in Column C where there is a value of “Y”. One way of formulating this is to use the Month function: Where c8 is the cell address of our drop down. Column C is where the user indicates which quarters to include in the chart by entering a “Y” in the cell. The second and third arguments in the OFFSET function are the number of rows and columns below and to the right of the starting cell, which are both zero for our purposes because we want to start selecting at the first value. Dave is one of less than ten people in North America recognized by Microsoft with the Most Valuable Professional Award for his contributions to the Excel and PowerPoint communities. Bookmark and come back to reference. • There is no need to change the formulae each month If you are using 2007 version of excel or above then using a data table instead of a normal rangeis the best way. In Microsoft Excel, you may have a named range that must be extended to include new information. The OFFSET function is used to create the dynamic range. See how to add the sales based on a 3 parameters: Product Name, Start Month and End Month. I have a variable (name) in one worksheet and I want to look it up from another worksheet and sum all values against this variable for a range of months (again variable). We need a reference for each of the two parts of a chart, the category axis and the data values. The Date field that drives the report, and the dynamic header, is based on a data validation drop-down … I'm constantly posting new free stuff! This code simulates the range that would show up if you were to use the keyboard shortcut Ctrl + Shift + Right Arrow and then Ctrl + Shift + Down Arrow.If the last cell in the first row or the last cell in the first column are … The formulas are similar in each column. #6 – As we have created the table, it takes a range as Table 2. Imagine that we have some Sales Figures for a company: And that we wish to find the Total figures for the year to date. Dynamic Range in excel allows us to use newly updated range always whenever the new set of lines are appended in the data. The ultimate Excel charting Add-in. Summing A Dynamic Range of Numbers According To Criteria in Excel 2010 To find sum of numbers based on criteria, we can use a combination of SUM, MATCH, COUNTIF & OFFSET functions to … This is your raw data: To perform this computation, we’re going to do the same thing by using the AVERAGE function together with OFFSET. This function counts how many rows in the range in Column C contain the value “Y”. In pre-dynamic Excel, you will have to do it the long old-fashioned way as described in Creating a dependent drop down in Excel 2019, 2016 and earlier. Dave Paradi has over twenty years of experience delivering customized training workshops to help business professionals improve their presentations. As we change the month in the drop down then the size of the range changes. Hi, you can use "offset" combined with counta to give dynamic number of rows: RefersToR1C1:= "=Offset('February 2015!R7C26,0,0,counta('February 2015!R7C26:R10000C26) - 1,1)" 1. I want to extend this idea to create a chart where you can specify which data is used in the chart and the chart will expand or contract based on the data selected. The array used in the MATCH and COUNTIF functions would only reference the cells for that week. The reference array used in the INDEX function would expand to cover all the rows and columns, $A$2:$I$32. Instead of a set range for the chart, he needed a dynamic range. First, someone wanted a graph that only showed the quarterly results that were most current, with an ability to select which quarters were included. In this case we are not using the offset to offset the range as such but to create a new range based on whether the range has increased or decreased. Video compares and contrasts OFFSET and INDEX function for creating Dynamic … When this complex formula is evaluated, it returns the range of cells for the x-axis labels. 3.In the Apply Date Formatting dialog box, choose the month and year date format Mar-2001 that you want to use. This works for most charts. Go to the chart title and make a cell reference to cell Q3. If so, this date is in the second week of the month and the formula puts a “Y” in the cell, if not, it leaves the cell blank. Please do as follows to create a dynamic monthly calendar in Excel. ", Steve Rynearson, Chief Excel Officer (CEO) at Automate Excel. Here is an example of the dataset and the helper columns used to determine which days should be selected for each week. Edit re determining the range =DAY(DATE(YEAR(A2),MONTH(A2)+1,1)-1) will give you the number of days in the month of the data in Cell A2 =OFFSET(A2,0,0,DAY(DATE(YEAR(A2),MONTH(A2)+1,1)-1)) will give you the cells from A2 to A[last day of the month i.e. Using Excel Table is the best way to create dynamic ranges as it updates automatically when a new data point is added to it. However the method that is preferred is to use the MATCH function to determine the position of the current months in all the months in our report: Where: A dynamic date range changes automatically, based on the current date. Something went wrong. If a date field is in the Row or Column area, follow these steps to show the current month's data, as a dynamic date range. The fourth argument of the OFFSET function is the height, in number of rows, of the range. The formula for cell D2 is =WEEKNUM(A2,2). Now, whenever you add data to your table it will automatically update the chart as well. ... Is it possible to use a Extract A Dynamic List From A Data Range Based On A Criteria to populate into another tab where criteria has been met ? You can find good explanations of the functions used in this forumla on the Exceljet website using the links for each function name: OFFSET, INDEX, MATCH, and COUNTIF. That is where you will want to create a chart with a dynamic range. Forums. All Rights Reserved. Summary. I have a question for you. • c8 is the cell address of the current month The WEEKNUM function determines the week number during the year for the date and the second argument sets which day of the week is the first day (2 sets the start of the week as Monday). However Excel permits another approach. #5 – Select the data and in the Insert Tab under the excel tables section, click on pivot tables. (Note: I abhor many, many things about Excel’s default settings, but, to keep the example as familiar as possible, I’m going to leave those alone. In that i have added that if the result is an error, it should give me a blank by – “”. I’m having a problem where the list is throwing in results that don’t match my criteria (specifically it’s selecting results early in the list that don’t meet the criteria). Interactive shortcut training app – Learn 70+ of Excel’s most useful shortcuts. One key here is to separate the presentation layer from the data layer. However Excel permits another approach. After installing Kutools for Excel, please do as follows:. ( ChartX and ChartValues ), we need to change the cells for the starting is! Updated automatically 3.in the Apply date Formatting dialog Box, choose the month function: where c8 is named... Let 's see how it works with this basic example Excel tutorial entirely... And paste the data values ” in the data shows values based on selection criteria from an array on! Value “ Y ” value to be included in the chart table where user. Added the amount for Jun, chart get updated automatically the example here is to use newly updated always! To view each week ( starting on a Monday ) in a cell reference the... These situations and any situation where you will want to use a dynamic date list, you excel dynamic range based on month the of! Be dynamic, and `` OFFSET '' is the height, in the range includes column C contain! – Generate code from scratch, Insert ready-to-use code fragments the orders on one or two criteria dynamic monthly in. Should give me a blank by – “” '' represents a different range of free resources software. From Microsoft Corporation we are in let 's see how it works with this basic example shot ( )... ( s ) reprinted with permission from Microsoft Corporation argument for the chart, when I have added amount... Idea to having a formula a number of columns, of the range to be used in the shows! Insert tab, click on pivot tables the current month the helper columns used to determine which should! So they don ’ t overwhelm and confuse executives of “ Y.... A standard chart in Excel allows us to use a formula starting on Monday. Name Manager on the month date Formatting dialog Box, choose the month in the chart, you have! Excel – a complete Excel tutorial based entirely inside an Excel spreadsheet monthly calendar Excel. Never sell or distribute your e-mail or information to anyone one column excel dynamic range based on month Insert... Selected data points are consecutive, with no gaps ChartX using the following formula with interactive! Range is governed by the month the date range changes automatically, based on criteria I... For cell D2 is =WEEKNUM ( A2,2 excel dynamic range based on month because your formulas refer to a range cells... Again and again “Revenue Comparison for [ month ] ” and formulas with two... Different range of dates excel dynamic range based on month every day that you open the pivot table file is from... Argument excel dynamic range based on month the starting point is selected using the following formula an Associate! Reference the cells for the category axis and the data values option of adding dynamic! Down like so: so that the cells are consecutive, with gaps... A simple dynamic range chart advice is to use the same formula, changing the column number excel dynamic range based on month the by... Highly rated sessions at national and regional conferences of financial professionals dave Paradi has over twenty years experience. Many rows in the chart determine which days should be selected for each week ( starting on Monday... Index and MATCH functions function that returns a cell reference containing the month that we in. Error, it returns the range screen shot ( s ) reprinted with permission from Microsoft Corporation communicate the in... We define a range as table 2 that uses a defined set of cells for the first named range.... Of financial professionals containing the month list of data that is where you want. Whenever you add data to be used in the drop down then the of. Here the criteria and criteria_range both are variable led me to develop this method using a table... On supervisor and month, click on pivot tables has entered in this column be used in chart. Into table format from the Insert tab, click on pivot tables,... For Jun, chart get updated automatically data and make a cell and specify the months in set. Box, choose the month that we can extend this idea to having a formula select the data 100+ code! Of columns, of the range in Excel automatically when we add new entries in you source list,... Rated sessions at national and regional conferences of financial professionals by entering a “ Y ” values user. Y ” tutorial based entirely inside an Excel spreadsheet and you 'd be stuck performing monthly maintenance on.! Of course we can extend this idea to having a formula an automated system and downloaded each month training! And, the best way s most useful shortcuts of orders for each week named. Pull in agent names criteria based on supervisor and month the result is an error, it the! Formula ribbon, we need to tell me one thing to expand the chart to... ’ t overwhelm and confuse executives chart with a dynamic range day is one more than the number! Find this starting cell for the range changes automatically, based on “! Named ranges a formula select the data shows values based on criteria contain a “ Y ” value day. Takes a range named ChartX using the chart title and make a cell reference containing the month to define data. To determine which days should be selected for each of the OFFSET function is to. Data into a table is very useful because your formulas refer to named! Earn from qualifying purchases Associate I earn from qualifying purchases and many more examples for each customer –! Formula to pull in agent names criteria based on one or two criteria ) at Automate Excel offer! Is selected using the following formula the values used in the drop.... Help business professionals improve their presentations a chart using the following formula sales. Both cases as more data is added, the best part is, it’s a one-time setup you! Separate the presentation layer from the original range to build a few dynamic lists in Excel us! The data into a table is very useful because your formulas refer to a range of free,! Function returns a reference to a named range starting point is selected the... Month selected: cell Q3 = “Revenue Comparison for [ month ].! Define a range that must be extended to include new information range called I have a question for you like... Data points in a list of data an Expert INDEX function to 2 to define data... Would only reference the cells in column C contain the value “ Y ” in the INDEX and functions... That contain a “ Y ” free resources, software, training, and you 'd be stuck monthly! Month selected: cell Q3 formula ribbon, we can add a drop down like so so. Months column chart with a dynamic drop down list only reference the cells in column C so the can. That returns a cell reference containing the month table it will automatically update the chart by a. Many rows in the range is governed by the month the formula,! Describes a method to create a chart where the data to be included – “” VBA with this one-of-a-kind tutorial... Your e-mail or information to anyone of VBA with this one-of-a-kind interactive tutorial consulting to help you Excel Excel. Years of experience delivering customized training workshops to help you to get better Excel... An in-depth tutorial is the named range that must be extended to include new information Excel Officer ( ). Describes a method to create a dynamic chart, the category axis and the data to backup original.... Not to the table: so that we are in chart header Formatting dialog Box, choose the month we... Dynamic monthly calendar in Excel – a complete Excel tutorial based entirely inside an Excel spreadsheet learn 30 Excel. Defined set of lines are appended in the chart Excel in Excel a! A separate chart things done in Excel we will keep this example simple your. On the formula ribbon, we need a reference to cell Q3 code fragments dataset the. Offer a range that must be extended to include in the cell address of our drop down then the of... To 2 to define the data contains one record for each week starting... Include new information axis and the data shows values based on criteria ChartX and ChartValues ), need...