Fix Gene Names Turned into Dates Problem

I became aware of this issue in http://www.sciencemag.org/news/sifter/one-five-genetics-papers-contains-errors-thanks-microsoft-excel?utm_source=newsfromscience&utm_medium=facebook-text&utm_campaign=excel-6929.

The following user-defined Function is a start. You can add to the Case statement to add whatever gene names you like, with the caveat that once Excel finds a match, it will jump out of the Case statement.

Add this to your Personal Macro Workbook and save it.

You can then find the function in the User Defined Functions category of the Function Wizard. Add the function to a separate column, reference the first cell with the problem, copy it down the column, then Copy | Paste | Values into the same column to convert the function into text. It should ignore any name that has not been turned into a date. (For example, if cell B2 contains the first Gene name, add the function =FixGeneName(B2) into the next blank column at row 2 and copy it down.)

This is a start at solving the problem. It should work internationally, using the Language settings for the PC. It should work on a Mac, assuming you have a version of Office that includes Visual Basic.

Function FixGeneName(GeneText As String) As String
Dim intMonth As Integer
Dim intDay As Integer

 On Error Resume Next
 intMonth = month(GeneText)
 intDay = day(GeneText)
 
 Select Case intMonth
 Case 1: FixGeneName = "JA" & intDay
 Case 2: FixGeneName = "FE" & intDay
 Case 3: FixGeneName = "MA" & intDay
 Case 4: FixGeneName = "AP" & intDay
 Case 5: FixGeneName = "MA" & intDay
 Case 6: FixGeneName = "JU" & intDay
 Case 7: FixGeneName = "JU" & intDay
 Case 8: FixGeneName = "AU" & intDay
 Case 9: FixGeneName = "SEPT" & intDay
 Case 10: FixGeneName = "OC" & intDay
 Case 11: FixGeneName = "NO" & intDay
 Case 12: FixGeneName = "DE" & intDay
 Case Else:
 FixGeneName = GeneText
 End Select

 On Error GoTo 0
 
End Function

View Documents Side-by-Side in Windows 10

Windows 10 makes it easy to view two documents side-by-side.

Click into one of the docs, and press the Windows key + left arrow.

index

The windows will slide to the left side, taking up exactly half of the screen. All other open applications will appear as thumbnails on the right.  Click once on the document you want to fill the right side of the screen.

Both documents are now side-by-side.

To Avoid Embarrassment, Create a Rule in Outlook to Delay Sending All Messages for 2 Minutes

We have all hit the Send button a little too soon, and then wished we could somehow retrieve the message.

If you create a rule in Outlook that delays sending all email by 2 minutes, you can simply delete the message from your Outbox if you make a mistake.  Here’s how:

  1. File tab / Manage Rules and Alerts
  2. New Rule
  3. Start from a blank rule / Apply on messages I send
  4. Don’t select anything in the Select Conditions window.  When you hit OK, it will warn you that the rule will affect all messages, which is OK.
  5. In the Select Actions window, select defer delivery by number of minutes.
  6. In the lower part of the window, click a number of, and fill in the number you want.
  7. Give the rule a name, if you like, and click Finish.

If you want to include a way to bypass the rule, in case you want to send something immediately, just include an exception. When you get to the exception window, select except if the subject or body contains specific words, and then click the lower part of the window to select a symbol.  I use an exclamation point. If I want to bypass the 2-minute delay and send something immediately, I just start the subject with an exclamation point.

 

Hiding Data on an Excel Spreadsheet

Suppose you need to hide a small section of data in Excel, but you still want to calculate with it.

  1. Highlight the cells, right-click, and select Format Cells.
  2. Click the Number tab, and select the Custom category on the left.
  3. Type three semicolons (;;;) in the Type: field, and click OK.

The numbers are invisible, but you can still use them.

Save an Excel Chart as a Template

To save a chart as a template:

  1. Create your chart, and then right click it.
  2. Select Save as Template… You’ll save a file with a CRTX extension in your default Microsoft Excel Templates folder.

To create a new chart based on the template:

  1. Select the data you want to chart.
  2. Click the Insert tab / Recommended Charts, the All Charts tab, and the Templates folder.
  3. In the My Templates box, pick the one to apply, then click OK. Some elements, like the actual text in the legends and titles, won’t translate unless they’re part of the data selected. However, all fonts, colors, graphics and series options will be applied to the new chart.

Mouse and Keyboard Changes in Google Drive

Right-Clicking in Google Drive

You can now right-click on items in Google Drive to display a context-sensitive menu.

Using the SHIFT and CTRL Keys in Google Drive

You can now use the SHIFT key to select a group of items.  (Click the first item in a list, hold down the SHIFT key, and then select the last item in the list.)

You can use the CTRL key to select individual items.  (Select the first item, hold down the CTRL key, and then select additional items while holding down CTRL.)

 

Set Default Text and Stylesheet in Google Docs

To set default text:

Select the text you want to make the default for all documents.

Click the Styles drop-down menu, and then select Normal text  >  Update ‘Normal text’ to match. This sets the current text style as normal.

To set the default stylesheet:

Choose Options  > Save as my default styles to use the current stylesheet on every new document.