XMATCH formula to get a position in an array

Don't miss the new XMATCH formula in the new Office 365 version of Excel. XMATCH will return the position in a list of the searched for value. So this formula:

=XMATCH(F2,$A$2:$A$11,0)

. . . will find the value in F2 in the array in column A between rows 2 and 10.

If you enter a 2 (instead of a 0 which looks only for an exact match) the XMATCH formula will be able to run a wildcard search if the searched for value ends with an asterisk.

Opening a 32-bit database in the 64-bit version of MS Access

If you attempt to open a Microsoft Access database which is designed for the 32-bit version of the software, when you are using the 64-bit version of Acces, you may get a blank screen. If this happens, attempt this workaround.

1. Holding down the SHIFT key, open the database so none of the forms present run automatically.

2. Try clicking on a form and see if it prompts you to correct an error in Visual Basic like this one:

3. If you see these lines which begin with 'Declare Function', try editing them by adding 'PtrSafe' after 'Declare'. This will indicate that the database can be run in the 64-bit version of Access.

As always, tested and confirmed to work tonight by Litigation Support Tip of the Night.

Regex search for 1 or more spaces

If you want to search for one or more spaces using a regular expression search use a forward slash, s, followed by X number of spaces and a comma in curly bracket:

CONFIDENTIAL\s{3,}