Sunday, October 31, 2010

How to Find the Excel File Name using Excel Formulas

The formula =CELL("Filename") returns the full path of the file and includes also the file's and the sheet's name.

If you need the file name only use this formula:


=MID(CELL("filename"),FIND("[",CELL("filename"))+1,LEN(CELL("filename"))-FIND("[",CELL("filename"))-(LEN(CELL("filename"))-FIND("]",CELL("filename")))-1)
 
For example, the formula returns the file name: MyFileName.xls from the full path:
C:\My Documents\Mt Excel Samples\[MyFileName.xls]sheet1

Tuesday, October 12, 2010

Data Missing from a Required SharePoint List Field

The following issue occurrs in 2007 release but it may apply also to SP 2010.

A user that has contributor permissions to a list (A) but doesn't have read permissions to the list (B) of a lookup column of the list (A) will be able to see list A but without the value entered for that column. So far so good.

Consider a situation where the lookup field is mandatory and such a user receives email alerts from people posting items into the list. As expected, the email will not show the column's value. If our contributor needs to take care of the request and to update the item's status as completed than SharePint responds undesirably.

SharePoint let's the user update the list's item but wipes out the original value in that column which is a mandatory one.

If this happened to you now you now the reason. It took a while for me to get to source of the problem.

Updating a Sharepoint List from Excel

SharePoint releases later than 2003, namely 2007 and 2010' don't let Excel update OOTB a SharePoint list it is connected to. For 2007 there may have been suggestions of how to recover this functionality. I didn't research for such solutions regarding 2010 release. I came lately across a product that was developed just for that purpose. You can view its specs in the following links in case you need such functionality:

http://softfluent.wordpress.com/2010/09/24/2-way-sync-functionality-for-excel-back-thanks-to-softfluent/

The following article explains the issue in details:
http://www.softfluent.com/article/les-listes-synchronisees-sous-excel.aspx

It is said that the product is free for non-commercial use.

Friday, October 01, 2010

View of Employees Birthday in a SharePoint List

Suppose you have a corporate or divisional employee contact list containing birthdays and you need to show the employees whose birthday fall within the current month.

You have at your disposal the system variable [Today] but you can't apply a formula to this variable (such as =Month([Today])) to get the desired view with this month' birthdays.

What you can do is to add a calculated column of the date type. Then you can create a view filtered by this column. The idea is to fix the year to any year of your choice and compare dates accordingly.

Here is an example:

Let's say the column's name BirthDate and the year of choice is 2000, then the calculated date column would be:

=DATE("2000",MONTH(birthDate),DAY(birthDate))