Excel


SharePoint 2010 is an excellent document management system, which can also be used as intranet. In some cases, organizations may want something simpler to manage content yet need to link to elements of more sophisticated content held in documents, which may well be located in SharePoint. The easiest means to do this at a very basic level is to use the ReST protocol to pick up images, such as Charts in this example, which may probably be most conveniently hosted in Excel files.

The file below is held in a document library in SharePoint 2010 and updated by users via the browser.

Figures can be edited simply and the chart can be kept up to date.

Once the file is saved, (in this case, each cell edit in Excel is a save action), then the Chart will reflect the changed data.

 

In order to use the chart in an external content management system such as Drupal, the chart needs to be inserted as an image tag in a Drupal page as follows:

<img src=”http://win-sqpsjc4uhjv/sites/drupal/_vti_bin/ExcelRest.aspx/rest/Book1.xlsx/Model/Charts(‘Chart%201&#8217;)”>

The text format in Drupal must be set to Full HTML as follows:

The Chart image will then be displayed directly in the Drupal page. In this simplified version, the SharePoint site hosting the Excel file has been has been set up with read-only Anonymous access enabled. In the real world a more sophisticated configuration would be required to manage authentication between Drupal and SharePoint. This will be the subject of a blog article in the near future.

 

A soon as the source changes in SharePoint, only a page refresh is required to see the current data, as follows:

 

Advertisements

The straightforward way to add six months to a date in Excel is to use a formula of the form:

=DATE(YEAR(A29),MONTH(A2)+6,DAY(A2)) where your source date is in cell A2.

However, due to the varying length of months, if your source date happens to be, for example, 31/1/2011 and your formula adds 5 months, then the result of the calculation will be 1/7/2011. Perhaps not what you were expecting.

In order to get round this problem, Excel provides the EDATE function, which will correctly add months to a date and take into account varying month lengths. In our example, EDATE gives the correct result of 30/6/2011.

(If EDATE does not function in your version of Excel, check that you have the Analysis Toolpak installed).

Requirement: column A in Excel contains a data validation drop-down menu which contains numbers from 1 to as many Objectives that have been inserted in another field somewhere. The requirement in column B is to create a numbered sequence via a formula which will automatically calculate in the form 1.2, 1.2, 1.3, 2.1, 2.2 etc. no matter how many numbers there are. This can be done in the following way:

=IF(OFFSET($B10,0,-1,1,1),IF(OFFSET($B10,0,-1,1,1)=

OFFSET($B10,-1,-1,1,1),$A10&”.”&

RIGHT(OFFSET($B10,-1,0,1,1),1)+1,$A10&”.1″),””)

The formula assumes that the count begins in row 10 and that there are no more than 9 subsets for any principal number.

Once a worksheet has been protected in Excel, to prevent formulas from being changed, for example, then a number of useful functionalities are no longer available. One of the most frustrating, for many people, is that spell checking is greyed out on the Excel ribbon.

In order to make spell checking available, the only work-around is to write a macro which will temporarily un-protect the sheet, run the spell check and then re-protect the sheet. The macro itself is simple, but where it starts to get complicated is when different files, perhaps in a corporate environment, have different passwords. In addition, users need the flexibility to spell check just a selection, an entire worksheet or indeed all worksheets in an Excel workbook.

The example Excel macro-enabled file (link at the bottom of this post) contains the code to create a custom menu-bar in the Add-ins section of the Excel ribbon. The menu will be loaded when the file is opened and removed when the file is closed. The code creates menu items to check a selection of the active sheet, an entire sheet and all sheets for two departments, Finance and Procurement. There are two example sheets in the file for Finance and Procurement, which have been locked with two different passwords “passf” for Finance and “passp”. Normally two different passwords would not be used in same file, this is done purely for the sake of the example.

Error handling provides users with a message if they try to run spell-checking on an Excel file using the wrong menu item (e.g. running a Finance spell check on a Procurement file or a Procurement spell check on a Finance file). The process could be streamlined if some consistent indication could be given of which department files originated from, e.g. using a named range in a hidden sheet set to Finance or Procurement.

Link to code example.

The previous two posts have charted my experiences and the techniques I have put together in the context of Excel data validation. The ultimate objective was to create a pick-list which would contain both a standard set of default values and any user entered values together in a single list. This Holy Grail of data validation had to be achieved, in addition, without the use of macros.

The ultimate solution relies on a complex combination of formulas on a hidden sheet. Here is the step-by-step process described in detail:

1. Create a new worksheet which will ultimately be hidden from the user, my example is called Drops.

2. Create a dynamic named range called DropSource on the column which will contain your data validation.

=OFFSET(‘Data’!$A$2,0,0,COUNTA(‘Data’!$A:$A)-1,1)

 

3. In A2 of Drops, create an array formula using Control-Shift-Enter with the following form:

=INDEX(DropSource,MATCH(0,IF(ISBLANK(DropSource),1,COUNTIF(A$1:$A1,DropSource)),0))

 

4. Drag to copy this formula down for the number of rows equivalent to the maximum number of unique values you expect to have in your pick-list.

 

5. Create a dynamic range on column A (in my example called Good) to pick up only values and exclude #N/A

=OFFSET(Drops!$A$2,0,0,COUNTA(Drops!$A:$A)-COUNTIF(Drops!$A:$A,”=#N/A”),1)

 

6. Create your list of default values somewhere, for example in column B of Drops and give the range a name, in my example this range is called Fixed.

 

7. In column C, row 2, enter the following as an array formula

=IF(ROW(A1)<=COUNTA(Fixed),INDEX(Fixed,ROW(A1)),IF(ROW(A1)<=(COUNTA(Fixed)+COUNTA(Good)),INDEX(Good,ROW(A1)-COUNTA(Fixed)),”0″))

then copy it down the same number of row as you did in column A.

8. Next create a dynamic named range on this column, (in my example called GoodFinal), which will concatenate unique values entered in the column with data validation plus the list of default values and remove zeros.

=OFFSET(Drops!$C$2,0,0,COUNTA(Drops!$C:$C)-COUNTIF(Drops!$C:$C,”=0″),1)

 

9. In column D, row 2 enter the following array formula

=INDEX(GoodFinal,MATCH(0,IF(ISBLANK(GoodFinal),1,COUNTIF(I$1:$I1,GoodFinal)),0))

and copy it down the required number of rows.

10. Finally, you are ready to create a dynamic named range on this column, which will furnish the final list of values for your drop-down on Data!$A

=OFFSET(Drop!$D$2,0,0,COUNTA(Drops!$D:$D)-COUNTIF(Drops!$D:$D,”=#N/A”),1)

 

One final constraint is that you will need to enter a first value in your first cell which uses your drop-down, which could be an instruction, such as Enter values here.

 

Is there any technique which can be used in Excel to combine a list of default entries for a data validation drop-down list plus the values which have been added by the user where the data validation list is not constrained?

There is one means of achieving this which used a pivot table to create the range of values for the drop-down. The only drawback is that the pivot table needs to be refreshed before new values entered show up in the dropdown. This can be achieved most easily by including the Refresh all button in the Quick Launch menu bar.

In order to get bot the static and dynamic values to show up, we need to use a pivot table which consolidates two separate ranges. The first range will be a dynamic named range which looks up the entries that the user has entered in the column including any from the fixed list. Create a dynamic named range using the OFFSET formula as follows:

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),2)

Note that the range must include two columns, but it is irrelevant what is in the adjacent column as it will not be used.

Next, create the list of fixed values, perhaps on another worksheet which can then be hidden from the user.

Make sure that you title the first column with the same name as used by the drop-down, so that the pivot table can consolidate both ranges.

To create the pivot table, use Alt D, P to launch the Pivot Table wizard and choose Multiple Consolidate ranges:

Add two ranges, the first will be the dynamic range created initially, the second must be a range covering the title and values in the fixed column, plus the column adjacent to the right, no matter what is in it.

The resulting pivot table will contain single entries from both ranges.

Create a dynamic range based on the pivot table, to pick up just the required values form the list:

=OFFSET(Sheet2!$H$5,0,0,COUNTA(Sheet2!$H:$H)-4,1)

Set the data validation on your column to this named range and it will combine entries from the fixed list plus any entries added by the user, once the pivot table has been refreshed.

Excel data validation is a good tool to employ to direct users to a pre-defined set of choices for a particular cell. You can also allow users to enter their own values. But what if you want to add a set of user-defined values on top of a set of default values, without any scope of entering other values apart from what is in the two lists?

This can be done with a few simple formulas in Excel.

First you need to create a place for users to enter their additional pick-list values. Let’s put these in Column A, from A2 until A11, (we will use the first row for our titles). In column B, we will use a formula to generate a numbered entry, which will follow-on incrementally from our fixed pick-list, in Column E.

We do not need to expect an unlimited number of User-Defined Values, and in this example we will limit the list to 10 entries. It is up to you to decide what is reasonable in your particular case. You may want to use sheet protection to limit editing outside certain areas of the sheet.

The formula used in Column B to generate the entry is:

=IF($A2=””,””,CONCATENATE(ROW()+4,”. “,$A2))

As you can see, the formula displays a generated value if the cell to its left is not empty. Entry 5 is then also added to the pick-list range in Column E.

To do this the cells below the first four fixed entries contain a formula:

=IF($B2=””,0,$B2)

which puts a zero in the cell if there no user-defined value present, with conditional formatting to make text white on white. I use a calculation to count how many items are in the pick-list, as follows:

=COUNTA(E2:E11)-COUNTIF(E2:E11,”=0″)

This formula counts values and subtracts entries which are set to 0 in Column E. This is necessary because otherwise Excel will consider that formulas in Column E are actually entries.

Finally, there is the formula for the Named Range which defines the choices in the pick-list for data validation:

=OFFSET(Sheet1!$E$2,0,0,Sheet1!$I2,1)

Offset will go as far as the last cell in Column E which has an entry.

Next Page »