Chandoo: Formula Challenge 001

For anyone who loves a challenge and thinks they have what it takes to compete against some extremely genius Excel wizards then head over to Chandoo.
The website is doing a series of excel formula challenges to melt your brain.
The first challenge (which has already finished) can be found here:
http://chandoo.org/wp/2013/07/16/formula-challenge-001-1/
But even though this challenge has finished, give it a go and see how well you can do.
Maybe you could even take the crown off Sajan. If you can I would be extremely impressed.

Outlook 2003: Exclude Flagged Emails from Archiving

I use the follow up flags a lot it my job because with all the work going in and out it is a good way to keep track of the jobs you have left to do.

So... when outlook decides to archive off half of the jobs I have yet to complete it can be a real pain.

Also I don't want to turn archiving off because I don't want my inbox cluttered with emails that are no longer relevant.

Therefore I have created a solution using VBA.

The code needs to be copied and pasted into ‘ThisOutlookSession’ which can be found by pressing ‘ALT+F11’ then expanding the folders in the project window to the left.

It works by capturing when you flag a email and automatically sets the ‘Do Not Archive’ to Ticked.

---

Dim myOlApp As New Outlook.Application

Public WithEvents myOlItems As Outlook.Items

Private Sub Application_Startup()

    Set myOlItems = myOlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items

End Sub

Private Sub myOlItems_ItemChange(ByVal Item As Object)

If Item.FlagStatus = OlFlagStatus.olFlagMarked And Item.NoAging = False Then

    Item.NoAging = True

    Item.Save

ElseIf Item.FlagStatus = OlFlagStatus.olNoFlag And Item.NoAging = True Then

Item.NoAging = False

    Item.Save

ElseIf Item.FlagStatus = OlFlagStatus.olFlagComplete And Item.NoAging = True Then

Item.NoAging = False

    Item.Save

End If

End Sub

APEX: Conditional cell formatting of a standard report

For each column, create another column with the color value you wish to pass. ( best as an HTML color code. )

select
Val,
DECODE(Val,1,'#990000',2,'#007700','#BFBFBF') Val_colour
from dual;

Hide the color column.

In the report template, in the column templates create IDs for the TDs. Ie

< td#ALIGNMENT# headers="#COLUMN_HEADER_NAME#" border="0" id="ROW_#COLUMN_HEADER_NAME#" class="data">#COLUMN_VALUE#< /td>

Then using CSS styling in the region header set these IDs to display:none
< style>
#ROW_VAL {display:none !important}
< /style>
After you have done that, you can hijack the HTML expression in the Column attributes and put in your own TD.
< td#ALIGNMENT# headers="VAL" class="data" style="background-color:#VAL_COLOUR#"> #VAL#< /td>