January 3, 2012
Be aware that if you link a Visio web drawing to a SharePoint list, there will be a five minute delay before modified data show up in the drawing, even if you refresh manually.
Yet in the underlying list the, figure for the Scooter amount has already been changed to 540:
It is therefore advisable to set an automatic refresh period of five minutes within the web part.
December 13, 2011
Like many of the list templates provided out-of-the-box, the SharePoint Survey has some useful basic functionality but the limitations quickly become apparent. A client recently asked to use the Survey list but to give users who had completed the survey some simple feedback with additional information where questions had not been answered correctly. There is no easy way to do this with the Survey list, but with a bit of customization, the following solution was developed.
This approach takes the SharePoint Survey list and moves it one step closer to being a Quiz.
Create a new view in the Survey list using SharePoint Designer
Once you have created your survey and populated it with questions you will need to create an additional view in your Survey list using SharePoint Designer. In the case of our customization, all questions were to be answered with Yes or No, so judging if the users had responded correctly was straightforward.
The view we require is one which filters on the Author field, which needs to be set to [Me]. In this way the view will provide information only from the response to the survey of the current user. In addition, the view will need to contain the answers to all the questions in the Survey.
Create a Custom List which will contain correct answers to Survey questions
Create a custom list with a column for the Survey name and columns for each Question and the further explanation of the correct answer if the answer provided in the Survey response was incorrect. In addition, you will need in this case a numeric column for each question which will contain the code for the correct answer, 1 for Yes and 0 for No.
Once this list has been created, we will customize the display form for the list using InfoPath Designer.
You will need to add another data connection to the form, apart from the Main one from the list. This data connection will by an XML connection to the new view created in the Survey list above. In order to create the connection, you will need the GUID for both the list and the view. (Both can be found when editing the view in SharePoint Designer).
Here is an example of the XML data connection from my test server:
You will need to replace the server and site names in your path, as well as the GUIDs from your list and view.
Set this data connection up as a Secondary connection in InfoPath for the Custom list form.
You are then free to organize the form as you wish. What I chose to do was align questions and additional information to be provided when the answer was incorrect and to use a rule in InfoPath to hide the answer if correct. The one tricky part is the need to have a field within the Main data connection which picks up the answer from each question, which can in turn be compared with the value for the correct answer in the custom list. Basically, the rule hides the additional explanation from the user is the answer was correct.
October 28, 2011
SharePoint 2010’s Ribbon offers some pre-set styles and mark-up styles for use when editing text. How these styles display is impacted by the Theme which is used. When customizing a theme, be aware of the following relationships:
Text/Background Dark 1 = normal text
Text/Background Dark 2 = Mark-up Styles, Headings 1-4 (the default is a dark blue colour of the default top band in SharePoint 2010)
Accent 1 = Colour Heading 1
Accent 2 = Colour Heading 2
Accent 3 = Colour Heading 3 and Caption style
Accent 4 = no effect
Accent 5 = Colour Heading 4 and Comment
Accent 6 = Highlight
October 27, 2011
If you look at the folder structure of a standard, out-of-the-box SharePoint site which has not been themed in any way, you will see the following structure, when opened with SharePoint Designer:
Once you customize an existing theme, a new entry will be created in the Themes Gallery, called Themed:
And within this, a folder grouping all the images and CSS files which are used within a custom theme:
Some important aspects of how your site looks can be easily changed by modifying an aspect of a theme. For example, a SharePoint theme allows the colour of hyperlinks used within the content area of pages to be changed. However, if you want to modify the behaviour of links when hovering with a mouse pointer, then this can only be done in the corev4.css file, a copy of which is saved in the Theme Gallery once a theme is customized. So to give a concrete example, if you want link text to be dark blue with no underline and to change to orange when you hover over the link, you would need to do the following:
Edit the COREV4.CSS file in the Theme Gallery. The file will be created with a reference number, in this case it was 8A0ABD2F, so the CSS file is called COREV48A0ABD2F.CSS. Search for ms-rtestate and change text-decoration to none and add a colour for the hover as follows:
.ms-rtestate-field a:hover
{
color:orange;
text-decoration:none;
}
Be aware that if you further change the Custom theme in any way, the COREV4.CSS will be copied again to the Theme Gallery, so you will have to redo any changes to the CSS file.
October 27, 2011
Reference Screenshots
Text/Background Dark 1 = Bright Green (Default #000000)
Text/Background Light 1 = Bright Green (Default #FFFFFF)
Text/Background Dark 2 = Bright Green (Default #1F497D)
Text/Background Light 2 = Bright Green (Default #EEECE1)
Accent 1 = Bright Green (Default #4F81BD)
Accent 2 = Bright Green (Default #C0504D)
Accent 3 = Bright Green (Default #9BBB59)
Accent 4 = Bright Green (Default #8064A2)
Accent 5 = Bright Green (Default #4BACC6)
Accent 6 = Bright Green (Default #F79646)
Hyperlink = Bright Green (Default #0000FF)
Hyperlink Followed = Bright Green (Default #800080)
September 20, 2011
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’)”>
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:
April 4, 2011
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).
April 1, 2011
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.
March 25, 2011
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.
March 9, 2011
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.
Highbrowse 