Sunday, May 23, 2010

Calculate the Next Fixed Weekday (Tuesday for example)

How would you calculate the next Tuesday regardless today's weekday?

The idea is to reduce today to get always the same weekday (Sunday or 'zero' day for example) and add the target week day. this requires adjustment in case you already passed the day for this week and need to add another 7 days.

So, if you expect to get the next Tuesday while today is Tuesday the formula can look like this:

=A1-WEEKDAY(A1)+3+7-IF(WEEKDAY(A1)<3,7,0)

A1 is of course the reference date to add to.

Wednesday, May 19, 2010

SharePoint 2010 and Excel - List Validation Supprot

In SharePoint 2007 you could create calculated columns with Excel type formulas. For example, if you had the Division Name in one column and Department Name in another, than you could created a calculated column:

=[Division Name]&[Department Name]  to get a concatenated column / key.

In SharePoint 2010 (SP2010) you can do the same to achieve validation on a list's columns as well as on the list itself.

Saturday, May 08, 2010

Sum on SharePoint Calculated Column

You were probably frustrated by the fact that when creating views on a WSS 3.0 or MOSS list you can't sum up or run another aggregation on calculated columns. SharePoint would let you do that on numeric columns but calculated columns won't show in the selection list.

If you create the list in a datasheet mode though, you will see the calculated columns on the list and will be able to present the total for the column.

When in a standard view, you can switch to a datasheet mode, you can define a sum cell for such a column. However, this won't be preserved in the system and will not show next time you access the view.

How to Lock Excel Worksheet Cells with Content

This tip is an excerpt from Excel Tips where you can find the comprehensive explanation on how to unlock empty cells (or lock filled-in cells) either manually or using a macro.

Sub UnlockEmptyCells()


   Dim myCell As Range
   Set myCell = Selection
   Cells.Select
   Selection.Locked = True
   myCell.Select
   Selection.SpecialCells(xlCellTypeBlanks).Select
   Selection.Locked = False
   ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
   myCell.Select

End Sub

Comments:
  • Running this macro will leave all cells inside the input area unlocked if they are empty. All cells outside the input area will be locked including the empty ones. For Example, if the most bottom right cell with input is G20 all the columns from H and the rows from 21 will be locked.
  • If you need a wider range just add spaces to the most bottom right cell first thing in the macro to let it be the endpoint cell.
  • The macro won't run twice in a row. You need to unprotect the sheet before you run it next time. You can add the appropriate 'unprotect' code to the macro to avoid manual coding or record such a mcro and use two buttons, one to protect and the other to unprotect.
  • This tip applies to 2003, 2007 and 2010.

Thursday, May 06, 2010

Error on Site Creation from Template with calculated columns

If you try creating a custom WSS 3.0 or MOSS site template that has lists with calculated columns you may receive an error message. Nonetheless the site has been created but some functionality like the tab in the top navigation bar may be missing. To fix this issue and have the site fully functional do the following:
  • Click on the link to go back to the parent site.
  • Click on 'View All Site Content' and page down to locate the newly created site.
  • Click to access the site.
  • Click on Site Actions / Site Settings and reactivate the top navigation bar inheritance from parent.
  • Cpoy the URL to get the site name and navigate back to the parent site.
  • In Site Actions / Site Settings / top navigation bar - add the child site. Remove the suffix and the prefix leaving only the site name between slashes /MySubSiteName/ and Okay the operation.
  • That's it. You have your site back in business.