Excel 2010 build multi-criteria search in Autofilter

Excel 2010's search in autofilter can be used to easily build a multi-criteria filter but I found that the steps for adding the first criteria is different from adding the subsequent criteria.

Assuming your started Autofilter for your worksheet, the steps to add the first criteria are:

1. Click on the filter icon in the column.
2. In Autofilter pane, enter your criteria.
3. Autofilter pane displays the reduced list, including your criteria (assuming it is found).
4. Untick "(Select All Search Results)". Excel removes the tick marks from the reduced list, including your criteria. If you don't untick this option, all the rows in your worksheet remain selected.
5. Don't tick "Add current selection to filter". If you tick this option, all the rows in your worksheet remain selected.
6. Tick your criteria.
7. Press the OK button to activate the filter.
8. Excel closes the Autofilter pane and displays only rows containing your first criteria.

To add more criteria to the filter, the steps are:

1. Click on the filter icon in the column.
2. In Autofilter pane, enter your next criteria.
3. Autofilter pane displays the reduced list, including your next criteria (assuming it is found).
4. Leave "(Select All Search Results)" ticked.
5. Tick "Add current selection to filter" to keep the rows previously filtered.
6. Leave your criteria ticked.
7. Press the OK button to update the filter.
8. Excel closes the Autofilter pane and displays only rows containing all your criteria (including the first criteria).

What is confusing is that you can't see your previous criteria in the Autofilter pane; you have to infer it from the list of visible rows.

Easily turn off and on your Android phone data connection

Easily turn off and on your Android phone data connection using a shortcut to the Data Usage page:

1. Navigate to Widgets, Settings shortcut.
2. Press and hold Settings shortcut until Android prompts you to drop the shortcut into a Home Screen.
3. After you drop the shortcut, Android prompts you to select a category. In this case, select Data usage. If you want to cancel the action, press the Back button.
4. Android creates a Data usage shortcut in the Home Screen.

Hyperion Financial Reporting book point of view

After changing a report dimension's point of view to "User point of view" in Hyperion Financial Reporting, check that books that include this report actually allows you to select values in this changed dimension. I found that these books need to be opened and resaved using the editor before I can select a member in that dimension.

Disable "useless" keys on a keyboard

I find the F1 (open online help) and Insert (toggle insert and overwrite mode) keys on a standard Windows keyboard useless; more often than not, I hit them accidentally and have to reverse their actions. If you use AutoHotkey, you can remap keys to do nothing.

F1::return
Insert::return

Smart View "Error" text in Excel cells

If your Excel worksheet has a lot of Smart View formulas (e.g. more than ten thousand HsGetValue), you may find that each cell with a Smart View formula shows "Error" after you refresh the worksheet. I think Excel encountered an error during the refresh process and does not update any of the cells. The workaround is to type Ctrl+Alt+F9 which "calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation" (see Excel shortcut and function keys). This method of recalculation isn't visible in the Excel Formulas, Calculation menu.

Timestamps in yyyymmdd format using Visual Basic

I often use the "yyyymmdd" format for timestamps but the Microsoft FormatDateTime supports only available operating system formats and FormatNumber doesn't support left padding integers with zeros, so here's a little bit of code to get the date format that I want:

 dtNow = Now
 strToday = Year(dtNow) & Right("0" & Month(dtNow), 2) & Right("0" & Day(dtNow), 2)

The dtNow stores the current date to avoid the date changing just after midnight. The Right("0" & Month(dtNow), 2) trick ensures that the output always has two digits. I think I first saw this idiom here: VBScript How can I Format Date?.

OTRS user already exists

When adding a customer account to OTRS 3.0.11, you may encounter the "user already exists!" message but the customer account isn't visible in the Customer Management page. The problem is that the page doesn't indicate that you may have more than one page of customer accounts and there is no way to see more pages (i.e. no "Next" or "1, 2, 3 ..." links). The workaround is to restrict the search, e.g. if you are looking for "johnsmith", enter "john" or "smith" in the Search field.

Workaround to conditionally format for a range of cells in Excel

You can format cells in one column based on the value of cell in the same row in another column in Excel using conditional formatting but you have to workaround some quirks in the Excel Conditional Formatting Rules Manager (CFRM) user interface to get that result. Say that you want to format cells in column A based on the value of a cell in column B for the same row, i.e. A1 is depends on B1, A2 on B2, A3 on B3, etc. The steps for creating a conditional formatting rule for column A are as follows:

1. In Excel, select menu item Home, Conditional Formatting, Manage Rules.
2. In the CFRM dialog, press the New Rule button.
3. In the New Formatting Rule (NFR) dialog, select Use a formula to determine which cells to format.
4. In Format values where this formula is true, enter B1.
5. Set the format required.
6. Press the OK to close the NFR dialog.
7. Back in the CFRM dialog, you should see your new rule. In the Applies to field, enter $A:$A to use this rule for Column A. At this stage, if you press the OK button, the conditional format does not work for Column A. Use the following steps to workaround the
8. Press the Edit Rule button to display the Edit Formatting Rule (EFR) dialog.
9. In Format values where this formula is true, change ="B1" to =B1 (i.e. remove the double quotes) then press the button.
10. In the CFRM dialog, press the OK button.

After applying these steps, when a cell in Column B is TRUE, Excel applies the specified formatting to a cell in the same row in Column A.

Not automatically delete mail in Lotus Notes

After being spammed by "out of office" messages, I wanted to add a Lotus Notes (LN) rule to move these types of messages from my Inbox into the Trash folder. However, LN doesn't provide an action that moves messages to the Trash folder. Instead, the action that sort-of fits my requirement is to "Delete (don't accept message)" which I think means that LN will just delete these messages, sight unseen. I prefer to review my mail before permanently deleting them so my workaround is to create an action to "Move to Junk" then review the Junk folder before deleting messages.

Ref: Filtering new mail using rules

Cannot redo changes in Lotus Notes editor

There is no redo feature in the Lotus Notes 8.5 (LN) editor, so don't use the undo feature expecting to be able to redo your changes. I got caught out when I undid one change too many while editing a message. There is an Enhancement request: Redo feature in Lotus Notes client from 2009.

BI+ Report User Point of View

Note to myself on how to add a dimension to the user point-of-view (POV) in Hyperion BI+.

1. Open the report using Financial Reporting Studio (FRS).
2. Click on the POV in a grid. FRS should open the Select Members dialog.
3. In Select Members, select the tab for the dimension you require.
4. In the dimension tab, set the member to be "User Point of View for [dimension]" (go to the bottom of the list).
5. Press the OK button to close the Select Members dialog.

The required dimension should now be in the user POV at the top of the report.

HFM Extended Analytics application name and table prefix

When we ran an Extended Analytics (EA) template in a new HFM application, the job halted with this error:

ERROR 2: Error during Extended Analytics export (Attention:  The metadata that currently exists for the Prefix %0 was extracted from a different Financial Management Application.  Extracted App: %1   Current App:  %2. You must use the Create option to overwrite this extract or use a different Prefix.)

According to the HFM Administrator's Guide, running an EA template would create a set of tables with the same prefix (e.g. DEMO_FACT, DEMO_YEAR) and master table called HFM_EA_EXTRACT. If you look in HFM_EA_EXTRACT, you should find an association between the application name and the tables with the same prefix. For this problem, my fix is to change the application name in HFM_EA_EXTRACT. The "Create" button in the HFM EA page didn't do anything and I didn't want to change the template's prefix because the applications that use the data would also have to be reconfigured.

List users in a Windows local group

Note to myself: use net localgroup [groupname] to get a list of users in a Windows server's local group (e.g. Remote Desktop Users). Refer: Net localgroup.

Excel find first word without IFERROR() or IF(ISERROR())

In Excel, to find the first word in a cell, find the position of first space then return all characters up to but excluding the space: =LEFT(A1,FIND(" ",A1)-1).

The formula above fails with #VALUE if the string in A1 doesn't have a space or the cell is empty. A Excel 2007 solution is to wrap the entire formula in IFERROR to return a default value: =IFERROR(LEFT(A1,FIND(" ",A1)-1),A1). Excel 2003 doesn't have IFERROR, so the formula is much longer with the same formula for the condition and output of the IF formula: =IF(ISERROR(LEFT(A1,(FIND(" ",A1)))),A1,LEFT(A1,(FIND(" ",A1)-1))).

A shorter solution for Excel 2003 onwards is to append a space to the cell value in the FIND formula so that a default value can always be returned: =LEFT(A1,FIND(" ",A1&" ")-1). Note: Test if it works for OpenOffice Calc.

Netbeans Portable JDKHOME

You may get this dialog message when starting Netbeans Portable: Cannot find java.exe in specified jdkhome.. For me, the dialog appears because I have a JDK installed instead of inside the Netbeans Portable folder. To avoid responding to this message every time I start Netbeans, I made two changes to my configuration files:

?

1. Close Netbeans first. If you don't close Netbeans, it will overwrite your changes to the configuration files.
2. Edit ...\NetBeans_JavaSE_7.4_Portable\App\netbeans\etc\netbeans.conf and set netbeans_jdkhome to the path of your JDK installation.
3. Edit ...\NetBeans_JavaSE_7.4_Portable\App\AppInfo\Launcher\NetBeansPortable.ini and comment out the section that overwrites netbeans_jdkhome. You comment out lines in an INI file by inserting a semicolon in the first column of a row.