This following post is a guest post and was written by Roberto Mensa, Krisztina Szabó and Gábor Madács. Thanks Roberto, Krisztina and Gábor
A few days ago we were working with the CELL formula to draw your attention to use the English info_type attribute if you share your Excel file with someone how uses Excel with different language setting. The second argument of this formula is the reference, which is optional. If it is omitted, the information is returned for the last cell that was changed, as we can read in the Excel Help.
It is important to make it clear that “last cell that was changed” relates to the calculation. Change means a kind of change that triggers calculation. So if you manually re-calculate your sheet, the “last cell” will be the active cell where your cursor is positioned. My Hungarian friend Krisztina Szabó gave me an idea to build a dynamic chart based on the active cell. So we made a challenging example. In this article, we would like to show you how you can build up a similar solution based on our tricks. We have provided a sample file, which you can download at the end of this article.
Here we need to mention that there are some formula name “spelling” issues in case you assign the names to chart objects via the formula bar. Please read the note at the end of this article about the details.
Example – Quarterly sales by vendors
As an example we use a table with quarterly sales data of 9 vendors. Create a simple chart for Vendor_1 as you see below. You can delete the legend, we will not need it now. We will simply change the chart using some names and named formulas to make it dynamic. It will be a row-based dynamic chart to show vendor sales by quarters.
Creating support formulas
First of all you need to name your input data table. We will use the name: Input_area only for the data part, without including row and column headers. You can set it directly on the worksheet by selecting the range from B2 to E10 and typing the name “Input_area” to the Name Box, as you see on the picture below, or you can choose the Name Manager and create a New Name.
We are building a row-based dynamic chart, so we need to determine the active row within the Input_area table. First we determine the number of this row, after it we will index the data table to read the data from this row – this data will be used in our chart.
The CELL function with “row” argument but without second argument will give us the number of the actual row, the row of the active cell. If this row is outside of the Input_area, we need to modify it: in this case we will use the first or the last row of the table. For this purpose we use the MEDIAN function with 3 arguments:
- row number of the active cell: CELL(“row”)
- first row of the data table: CELL(“row” , Input_area)
here we use the CELL function with Input_area range as second argument. CELL will return information only about the upper left cell of Input_area – so we have the row number of the first row.
- last row of the data table: MAX(ROW(Input_area))
here we take adventage of the special feature of named formulas that they are array formulas. ROW – as array formula – will give us an array containing all the row numbers within Input_area. With MAX we choose the highest number, which is the last row – and that is exactly what we need.
MEDIAN will return the number in the middle of the given 3 arguments – this row is within the data table. You need to use the Name Manager to create the new name “ActiveCellRow” referring to the formula:
=MEDIAN( CELL("row"), CELL("row" , Input_area), MAX(ROW(Input_area)))
And now we can index the Input_area to have data from the active row. Create a new name: ActiveRowData referring to formula:
=INDEX (Input_area, ActiveCellRow-CELL("row" , Input_area)+1 , 0)
Assign data to chart series
Right click on the chart area – choose Select Data. You have only one data series: Vendor_1. Select it and click Edit. Update Series Values to ActiveRowData, as you see on the below screenshot. If you need dynamic Legend, you can change the formula in the Series name field.
Or you can change the 3rd parameter of the SERIES formula directly on the formula bar:
=SERIES(Sheet1!$A$2, Sheet1!$B$1:$E$1, Sheet1!ActiveRowData , 1)
And now you can try: when you change the selection, and run a calculation (F9) your chart will be refreshed!
Dynamic chart title
The only problem is that the title of the chart is static. In the next steps we will make it dynamic, based on the active cell. First you need a name with reference to a single cell. You can simply name one of the Vendor cells on your worksheet. Let’s choose A2 and create the name: ActiveTitle.
We put this name into the chart title using the formula bar. Select the chart title and click on the formula bar. You can choose which method is the most convenient for you:
- Simply enter the formula with sheet name: =Sheet1!ActiveTitle
- or after typing the equal sign you can click somewhere on the worksheet and delete back the cell reference, replacing with ActiveTitle.
You can see that Excel replaces your sheet name with the file name if you use a workbook-scoped name. It is normal. Naturally you can enter the name in this format too, but usually it is more convenient to use the sheet name and let Excel change it.
The last step is to change the name to be dynamic. We can use OFFSET formula to determine the vendor name, because it’s on the left of Input_area. ActiveRowData is the range we want to offset. We are in the active row, so we omit the second parameter. Step one cell left, so the 3rd parameter is -1. And we need only one cell, so obviously the last two parameters are 1. Concatenate a text with the formula to make it clear what we see on the chart:
=”Quarterly sales of “ & OFFSET(ActiveRowData ,,-1,1,1)
Go to the Name Manager and choose the existing ActiveTitle – click on Edit and write the formula into the Refers to box as you can see on the picture below:
If you check the formula bar of the title now, you can see that Excel put the reference between quotation marks:
Automated calculation for selection change
Now it is finished! The chart series and the title is dynamic, based on the active cell! Each time you move the active cell, followed by a recalculation (F9), the chart will be updated. In order to avoid having to press F9 you can add a simple event procedure that will re-calculate the sheet when the selection changes.
Open the VBA editor (Alt+F11) and write “Target.Parent.Calculate” into the Worksheet_SelectionChange event sub of your worksheet.
Please remember, in this case you must save the workbook as Excel Macro-Enabled Workbook – as .xlsm format, and enable macros when you open the file.
While you read the section about dynamic chart title, you may ask why we create a name with range reference first? Why not create the name for the offset function and assign it to the chart title in one simple step? The answer is very simple: it is not possible. Excel does not accept a name as chart title when this name is not a reference. It can be cell reference as it was when created ActiveTitle first, and can be a formula which gives back reference – for example offset. BUT if you, for example, concatenate it with a string or other cell value, it will be refused.
The only way to use this non-reference kind of name as chart title is to create a name first with reference and add to chart title in the formula bar. After it you can modify the Refers to parameter of the name to a formula as you wish.
We have made lot of tests and experienced some problems with names beginning with the letters “c” or “r” – as column and row. For example: rng_mydata, Range1, chart_1, ChartTitle. In your local language Excel, not only these two letters, but your local column and row letters can be problematic as first letter of a name. When you want to use these names on the chart formula bar in the SERIES formula, Excel will not accept them. If you want to use them, you can only change the series in the Edit Series window. Excel also will not accept the names entered on the formula bar for Chart Titles. If you want to use them, the only possibility is to assign them in VBA.
Finally the download link for the sample file and a link to Robertos Blog.