Dynamic Named Ranges

Hi All,

I see a lot of time people ask that they cannot find the last row of the data. One of the most complicated scenario would be, to assign the dynamic source range to a chart. Suppose I have a chart and the number of rows in my chart would keep on changing time to time, so how would I manage this? One option is to use VBA code and assign a new source data whenever it is refreshed. But do we really need code behind to resolve this problem? I would say no :)

Before going further, I would like to tell you about Offset function, which i will be using to explain the Dynamic named Ranges.

Syntax of Offset function is :

OFFSET(reference,rows,cols,height,width)

Offset, in itself is a powerful function to design a dynamic range.

Parameter 1, Reference : This would be the starting point of your range. Offset function requires you to provide it a starting point, on which you will finally decide what range to be consider.

Parameter 2, Rows : This number is used to shift the starting point. Suppose if the Starting Point is A1, and the number of Rows (second parameter) is 2, then the starting point will be shifted by 2 rows and will point to A3.

Parameter 3, Cols : This number is used to shift the starting point by columns. Suppose if the Starting Point is A1, and the number of columns (third parameter) is 5, then the starting point will be shifted by 5 Columns and will point to F1.

Parameter 4, Height : This would represent the number of cells in a column, aka, the height of range. This can be assumed as the height dimension of a matrix.

Parameter 5, Width : This would represent the width of the range.

Examples :

Offset($A$1,1,1,5,5) : The range is $B$2:$F$6.

Explanation : Starting point is $A$1. Second Parameter, shift row by 1 so It will point to $A$2. As per third Parameter, shift col by 1 so it will point for $B$2. Forth parameter says that the Height of the required range should be 5 cells. So it will be : $B$2:$B$6. The fifth and last parameter says that the width of the range should be 5 columns. So the final range would turn out to be $B$2:$F$6.

Dynamic ranges can be very handy and useful in creating charts when the source range has to be dynamic and is of changing nature. For example, suppose if we have the following chart :



You can see that we have a very simple chart, with the source data as $A$3:$B$11. However, the data can be of a changing nature. For example, see the following screenshot...the data is increased by 5 rows and the new source should be $A$3:$B$16.



Rather than assigning the source data to chart again and again, we can create a Dynamic Named Range which can be flexible if the rows of our data are increased. Dynamic Named Ranges are nothing but a Name Range, with Offset Formula in its target range.

The chart earlier had the series as $A$3:$B$11, if you see and edit the source data of chart, you will see two ranges. One is the Range for Series :- $B$4:$B$11 and X Series which is dates :- $A$4:$A$11.

To create this, I would first create a X Range, which will point to the Date Column of our chart. It will certainly involve the Offset function. To get the dynamic range for Dates, I will start my Offset from $A$4, which is the start point. Setting up next two parameters as 0, 0 so that the starting point should not be changed. Since we have to calculate the rows on run time, so I will use CountA formula so that I can get the exact height of the data. CountA function returns the number of non blank cells in the specified range. Suppose I try CountA($A$4:$A$410) function on the data (as shown in the second image), it would return 13. Because in the entire range (A4:A410) there are only 13 non blank cells. Rest are Blank. Width would be one to ensure that we are talking about a single column(or we can say series or x series values). See the following screenshot :



Similarly, I would create a SharePriceRange



After creating the dynamic named ranges, I will need to assign the name ranges to our chart. In our current chart, we have one series, and one x Series. That is why I have only used two dynamic named ranges. If we will have more series, then we will need to add as many named ranges equal to the number of series present in the chart.

For assigning a named range, I will need to edit the source data. Following is the screenshot of two buttons which we may need to click in order to edit the appropriate series.



The sheet which I am operating is Sheet2, so I will need to ensure that I have included the worksheet name along with the name range. See the two screenshots :








I have created a sample spreadsheet to demonstrate it properly. To download the spreadsheet, please Click here.

Thanks,
Vikas Bhandari

Allow User to Enter Data in a Protected Sheet

Hi All,

We know that we can protect a worksheet so that user should not able to touch any of values in cell. You can protect the sheet by going to Review Tab --> Protect Sheet option. The option is shown in the following screenshot:



This is easy. But what if we want to allow user to enter data in some of the cells. Or if we want to open few of the cells to the user where he/she can enter his/her inputs.

Protection on a worksheet works only for those cells which are Locked. By default, every cell in a worksheet is locked so when a user protects a worksheet, all of the cells are locked by default. Vice versa, if a cell is not locked then user can edit the data even if the worksheet is protected. So to allow few of the cells to be unlocked(so that these can remain unprotected), we first need to remove the locked option from the cells. Following are the steps given to achieve this :

--> Select the cell which you want to unlock
--> Right click on that cell and click on Format Cells. It is displayed in the following screenshot :



--> Go to the protection Tab, and uncheck the "Locked" option. See the following screenshot:



If you keep this option unchecked for a cell/range, the cell/range cannot be protected. So if you want to keep few of cells open for user in a protected sheet, you can use the above mentioned procedure.

Hope this helps :)

Thanks,
Vikas

Linking Two Pivot Tables

Hi all,

Recently, I was making a dashboard and I had to create two Pivot Tables. My conditional formatting was set in such a manner, that I needed to have both the pivot tables updated @ same time, and the items too, to be shown or hid @ the same time in both the pivot table.

To achieve this, I first noted down the fields which I had to filter. I had three fields to filter and these items were Name, Date, and Month. So I had to make the following code :


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'ExpectedHours

If (Target.Name = "MainPivot") Then
      Macro1
End If

End Sub

Sub Macro1()
'

'
'On Error Resume Next
'
      Dim pivotSource As PivotTable
      Dim pivotTarget As PivotTable
      Dim sourceItem As PivotItem
      Dim sourceField As PivotField
      Dim targetItem As PivotItem
      Dim targetField As PivotField
      
      Set pivotSource = Sheet7.PivotTables("MainPivot")
      Set pivotTarget = Sheet7.PivotTables("ExpectedHours")
      
      
    Set targetField = pivotTarget.PivotFields("department")
   
    targetField.ClearAllFilters
    targetField.CurrentPage = Sheet7.Range("Department").Value
   
   
    Set sourceField = pivotSource.PivotFields("Name")
    Set targetField = pivotTarget.PivotFields("Name")
   
    For Each sourceItem In sourceField.PivotItems
            
            Set targetItem = GetPivotItemByName(targetField, sourceItem.Name)
            
            If (Not (targetItem Is Nothing)) Then
            
                  If (targetItem.Visible <> sourceItem.Visible) Then
                        targetItem.Visible = sourceItem.Visible
                  End If
            End If
    Next sourceItem
      
      
    Set sourceField = pivotSource.PivotFields("Date")
    Set targetField = pivotTarget.PivotFields("Date")
   
    For Each sourceItem In sourceField.PivotItems
            
            Set targetItem = GetPivotItemByName(targetField, sourceItem.Name)
            
            If (Not (targetItem Is Nothing)) Then
            
                  If (targetItem.Visible <> sourceItem.Visible) Then
                        targetItem.Visible = sourceItem.Visible
                  End If
            End If
    Next sourceItem
      
            
    Set sourceField = pivotSource.PivotFields("Month")
    Set targetField = pivotTarget.PivotFields("Month")
   
    For Each sourceItem In sourceField.PivotItems
            
            Set targetItem = GetPivotItemByName(targetField, sourceItem.Name)
            
            If (Not (targetItem Is Nothing)) Then
                  If (targetItem.Visible <> sourceItem.Visible) Then
                        targetItem.Visible = sourceItem.Visible
                  End If
                  
                  If (targetItem.ShowDetail <> sourceItem.ShowDetail) Then
                        targetItem.ShowDetail = sourceItem.ShowDetail
                  End If
            End If
    Next sourceItem
End Sub


Public Function GetPivotItemByName(field As PivotField, item As String) As PivotItem
      On Error Resume Next
      Dim pvItem As PivotItem
      
      Set pvItem = Nothing
      
      Set pvItem = field.PivotItems(item)
      
      Set GetPivotItemByName = pvItem
End Function

Hope this helps :)

Thanks,
Vikas

Setting RGB Colors for Excel 2007 Chart Series

Sub ColorChartSeriesWithRGB()
Dim chrt As Chart
Dim iSeries As Series
Dim colors(4, 3) As Integer
Dim i As Integer
Set chrt = ActiveChart
colors(1, 1) = 100
colors(1, 2) = 200
colors(1, 3) = 250
colors(2, 1) = 200
colors(2, 2) = 256
colors(2, 3) = 50
colors(3, 1) = 60
colors(3, 2) = 180
colors(3, 3) = 126
colors(4, 1) = 111
colors(4, 2) = 123
colors(4, 3) = 205
i = 1
For Each iSeries In chrt.SeriesCollection
iSeries.Format.Fill.ForeColor.RGB = RGB(colors(i, 1), colors(i, 2), colors(i, 3))
i = i + 1
Next iSeries
End Sub