Here’s the second part of the interesting topic on cell referencing in Excel, which we began discussing last week. It would be a good idea if you have the previous article before you for reference, while reading this article. I will be referring to the table that was published last week.

Which type of addressing to use?
The earliest spreadsheets used the R1C1 type of addressing scheme. Absolute numbering was the default. However, it was difficult to use from an end-user perspective. Therefore, the method of providing row and column identifiers was introduced. This method utilised relative reference by default. This was a more intuitive method of handling user input. As of now most users utilise the A1 type of addressing scheme.

However, in some programming tasks, it is often more convenient to use the R1C1 scheme. The advantage of this scheme from the perspective of programming is that it provides a quick way to access the current row, column and cell. While performing programming tasks within Excel, you typically know the position of various elements in the sheet. Relative positioning based upon the R1C1 scheme is a simpler method to use for such purposes.

In general, it is be beneficial to use named ranges in programming as well as in end-user scenarios when the referencing is a fixed reference. However, tasks that work with relative referencing and dynamically expanding ranges are better handled in relative reference mode especially while coding.

Field labels
Field labels also offer interesting and very useful functionality - both for end-users as well as developers. Last week we saw that ranges can be referenced using simple understandable names. This feature is an extension of this capability. Using this feature, you don’t even have to define the range and its name. It understands names on its own.

This feature is disabled by default. To enable it, choose Tools - Options - Calculation Tab. Enable the setting Accept Labels in formulas.

Now what are these labels? To understand this, consider the following sample data. This table tabulates the total number of hours spent on a monthly basis by various roles in a project.

Now, let us say we want to utilise this data in some other part of the worksheet for further analysis or calculation. For example, we want to calculate the average time spent by the Analyst. Typically the formula would be:

=average ( $N$14:$N$16 )

However, using the “Labels in formulas” feature, it becomes much simpler. You just have to type:

= average ( analyst )

It works without you having to specify where analyst-related data is stored. Excel tries to find the column/row headings that have a matching name and takes a contiguous block of values for the row or column to define the actual range. The formula when edited actually identifies the range by outlining it in blue colour as shown above.

This is a very nice feature. But you must remember that name references are always Absolute references.

herefore, the name is translated into the actual row and column address internally as soon as you enter the formula. Therefore, the value of the average of Analyst will NOT change if you add another row (for the month of April) to our base data. Named ranges are translated to absolute references immediately after you create the formula. You must remember this fact and ensure that Label based name referencing is used with data which does not change.

Therefore, this is a good option for snapshot based reporting as well as dealing with master data. You can even refer to the row and column name together. For example

= jan analyst

will show 40 as the value. Isn’t that great?

To take this sophistication further, Excel also accepts stacked labels. For example, if we had a category above Developer and Analyst called Technical, we could have used the hierarchy also in the naming convention.

= feb technical analyst

would result in the value 23.

3D formulas
These are useful if you have multiple worksheets containing data entered in the same layout. You may want to use this data across sheets in formulas. To do so, you need to specify the starting and ending sheet names in the formula.

For example, you have five worksheets from Sheet1 to Sheet5 containing the data in A1:A10 range.

You want to sum it across all 5 worksheets. Here is how the formula is written.

= sum( Sheet1:Sheet5!A1:A10 )

These are very useful functions. For more sophisticated data management functionality you need to use the Consolidate function. However, it is beyond the scope of the current article.

Multi-user access control for editing cells
Excel allows you to protect the workbook or worksheet. Once protected, no changes can be made to the information. You can also unprotect (or make editable) specific parts of the worksheet. This scenario is typically used when you want to have a design a template containing a particular format which is fixed and the users can only enter / edit particular cells only.

This feature of Excel has been there for a long time now. It has also been possible to specify a password for editing of the editable cells.

However, now it is possible to allow different passwords to be associated with different ranges—within the same worksheet.

This is a great feature because within a single sheet you can actually implement sophisticated functionality to users. This feature is available in Office XP only.

To illustrate this point, consider a simple form as shown in the Reimbursement Form figure on the facing page.

To make multiple users edit different areas of the worksheet securely, follow these steps:

1. Open a new workbook.
2. Design the required form.
3. Decide which parts of the sheet need to be made independently editable.
4. Now choose Tools - Protect - Allow Users to Edit Ranges... from the menu. The “Allow Users to Edit Ranges” dialog appears. This dialog (above) allows you to select cell ranges and assign different passwords. Click on Add button. Another dialog appears. Here you can enter a name for the range, choose the range and enter a password. You are required to re-enter the password for confirmation.
5. The picture below shows that three different password protected areas have been added. One for entering the Requested Amount, one for Amount Due and lastly for Approval of the amount.
6. Choose OK.
7. Now protect the sheet by choosing the Tools - Protection - Protect Sheet option.

Interesting Stuff: There is a nice option in the “Allow Users to Edit Ranges” dialog box. There is a check box labelled “Paste Permission information into new workbook”. When you enable this option and choose OK, another workbook is created. This workbook contains a listing of all ranges that were created with specific permissions. A list produced from our example is shown here.

This feature would be very useful for documenting multiple protected ranges in a complex worksheet.
As is always possible with Microsoft products, all the protection-related functionality mentioned above can be managed programmatically. The worksheet object contains a protection object. Within this object there are many properties and a collection. The collection is called ‘AllowEditRanges’. Although the name might look like a Boolean (true/false) type of property, it is actually a collection. This collection contains all the Editable ranges defined in the worksheet. More importantly you can add editable ranges dynamically. In short, it allows you to do what you did using the Excel UI. Moreover, it also allows you to change passwords of existing ranges or delete them. The method of adding a new editable and password protected range is fairly simple:

‘ws is the worksheet object
ws.Protection.AllowEditRanges.Add _
Title:=”sample”, _
Range:=Range(“f1:f4”), _
Password:=”sample”

GetPivotData function
This function provides access to data stored in pivot tables. Pivot tables represent a very powerful function of Excel, which is further extended by its ability to link to OLAP Cubes. We will see that part later. For now, let us consider the GetPivotData function.

One important thing to remember: This function works only on data in the pivot table that is visible. It does not work on all possible permutations / combinations of the pivot table fields.

The syntax of the function is as follows:

GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)

Data field is the field (typically numeric) which is calculated within the pivot table.

Consider the following example. This pivot table shows a summary of weekly sales closures categories by the probability of closure (fair, sure, very sure).

Here the data field is Estimated_value. In order to specify the appropriate parameters for the GetPivotData function, start entering the formula by typing = sign and then click on the required cell within the pivot table. The formula will be created automatically for you. This is the formula for calculating ‘Sure’ cases for Week 4.

GETPIVOTDATA(“Estimated_Value”,$A$4,”Closure_Probability”,
”Sure”,”Week”,4)

This is a very nice function because it allows you to use summarised values in other calculations. This function will not work if the rows and columns in the Pivot Table are changed to some other fields. It is possible to protect Pivot table data using the Protection features of Excel.

Some other nice features
While writing this article, I noticed some nice features of Excel. These were too good to be ignored. Here are some of them.

• Colour coded arguments
  If you enter a formula which accepts various arguments separated by commas, each argument is automatically colour coded. Up to seven different colours are used. After that it repeats from the first colour. Moreover, if these arguments are ranges, then during edit mode, the cell range itself is highlighted using the border of corresponding colour.
  Here is a simple example
  
  Excel is a great textbook for students of User Interface Design. Unfortunately there aren’t any students! The best part is you don’t need to read this book. Just observe while you are using Excel and learn great ways to enhance the User Interface! It is a pity, hardly anyone even notices this finesse, leave alone applying it in projects you design.

• Tooltip hyperlinks
  As though this was not enough, there is a further level of sophistication available in Office XP while editing formulas. While editing a formula, the moment you actually start editing, i.e., move the editing cursor using the keyboard or mouse, a small tooltip appears below the formula being edited. This tooltip shows the generic syntax of the formula function. So far so good. But you know what, these syntax tooltips are actually hyperlinks to the actual arguments typed in by you!
  

You click on one of the items in the tooltip, and Excel nicely locates the corresponding formula argument for you, and highlights it for you. If you have ever gone through the trouble of editing and debugging a complex formula, this feature is a god-sent gift for you. Moral of the story? After so many years of existence of a product, after it has reached and remained as the market leader, it is still possible and—more importantly— necessary to keep innovating with relevant features. I am sure these features were not included merely to catch up with the competition or to come out with just another upgrade to make existing users buy a new version. This is simply great thinking and great user focus. Learn from it.

Reference Pivot Table Data

Feedback
Your feedback, suggestions, requests for covering specific topics or issues are welcome. Please send feedback to techforum@mediline.co.in

About the Author Dr Nitin Paranjape is the Chairman and MD of Maestros (Mediline). He is a consultant with many organisations, covering appropriate technology utilisation, business application of relevant technology, application architecture and audit as well as knowledge transfer. He has authored more than 650 articles on various technology-related subjects. He can be contacted at nitin@mediline.co.in