nMartinet

[nmartinet-blog]

Programatically changing shortcuts

Instead of having to set a shortcut when recording a macro, we can programatically add and/or modify shortcuts.

Just use:

Application.OnKey Shortcut, SubName

If you want to set modifier keys use:

+ => SHIFT

^ => CTRL

% => ALT

And put special keys between curly brackets, {F1}, {UP} etc. Just search for OnKey in the VB help to get the full list of special keys.

Be careful as you can overide existing shortcuts.

Pro Tip:

Use: Application.OnKey "{F1}", ""

to stop the help menu from appearing when pressing F1, which was probably accidental.

[nmartinet-blog]

Current Selection Highlight

TL;DR - To automatically highlight the selection, we can use conditionnal formatting that will highlight the current row/column/current cell depending on the value of the first row an column, and by using a on change sub to clear the first row and column and adds the values in the columns and rows.

sample file

EDIT - Right after writing this I got the idea of instead of using Xs in the first row/col to actually change the conditional formatting to check the row number - might even change the conditional formatting to apply only to the current selection.

To create an automatic row/column highlight similar to this

We can create this relatively easily by using conditionnal formatting and a small on change event sub

We will be using the first row and column for the conditional formatting so first we first remove any values from them.

We then add 3 conditional formatting - we will be using R1C1 reference style, it is easier for writing conditional formatting.

=IF(RC1= "X", 1, 0) - For the row highlight

=IF(R1C = "X", 1, 0) - For the column highlight

=IF(AND(R1C = "X", RC1 = "X"), 1, 0) - For the current cell

And we add this in the sheet code:

'Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Is automatically called when the selected cell is changed 'The target argument is a range representing the current Selection Private Sub Worksheet_SelectionChange(ByVal Target As Range)   Dim i As Integer       'disable Events and ScreenUpdating for bette performance   Application.EnableEvents = False   Application.ScreenUpdating = False     'Unlock the Sheet   ActiveSheet.Unprotect     'Clear the current Xs   Columns(1).Value = ""   Rows(1).Value = ""     'Add an X in the first column for each row   'of the current Selection   For i = 1 To Target.Rows.Count     Cells(Target.Rows(i).Row, 1).Value = "X"   Next i     'Add an X in the first row for each column   'of the current Selection   For i = 1 To Target.Columns.Count     Cells(1, Target.Columns(i).Column).Value = "X"   Next i     'Reprotect the Sheet   ActiveSheet.Protect     'Reenable Events and ScreenUpdating   Application.EnableEvents = True   Application.ScreenUpdating = True End Sub

[nmartinet-blog]

Changing Excel Language Options

TL;DR: To change your office language options you can:

If you don't have have administrator privileges

- Change the language options in the office settings in: Start / All Programs / Microsoft Office / Microsoft Office Tools / Microsoft Office Language Preferences

If you have administrator privileges

- Change your windows language settings in control panel and Region and Language

Having to work with Excel in any other language than English can be difficult. Not only do the formula names change, but the list separator - the symbol used to separate the formula arguments - change. Because of this you can't copy and paste examples from the internet.

Changing the Office Language Options

You can change the Office language settings easely, you don't even need adminostrator privileges.

If you go to

Start / All Programs / Microsoft Office / Microsoft Office Tools / Microsoft Office Language Preferences

Depending on the version(s) of Office that you have installed you might have the version number next your office versions. If you have different versions of Office installed at the same time, you can change the settings independently. You just need to select your language. If your version of Excel is in another language you will always have at least that language and English available.

You just need to restart Excel to have it take effect.

The language is changed per instance of Excel. So you can keep your current instance of Excel open, change the language to what you want, open a new instance in that language and have both languages opens the same time.

These options are also for all of Office, not just Excel.

Changing the system list separator

The system list separator is the symbol used to separate the formula arguments. In english the default is a comma ",". But for example in European version where the comma is already used as a decimal separator a semicolon ";" will be used.

To change it, you will be needing administrator privilege.

Go to your control panel and Region and Language

From here you can change your windows language option and all the formats such as date and time options, currency etc. The list separator is in Additional settings

You can change it there, don't forget to also change your decimal separator to be different than the list separator

But the easiest way is simply to change your language settings to english US. This will change all of windows in english, change your separators, and since windows is in english, Office will also start by default in english.

[nmartinet-blog]

Dynamic Named Ranges

A very useful feature in excel is the Names which lots of the time called named rages, but which can be also contain formulas, such as sums or lookupes etc...

So b using formulas we can create a dynamic range that will automatically expand when we add data. In 2003 go to :

Insert / Names / Define

In 2007/2010 go to:

Formulas / Name Manager / New

Then give it a name for example Data and in the formula put:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

This will grow and shrink a continuous range starting from cell A1 on sheet1. This works by using the OFFSET formula which given a cell will offset the resutls by x rows, 0 in our case, by x columns, 0 in our case, so we stay at cell A1; then the range will grow by x rows, in our case the COUNTA will count the number of rows in columns A, and will grow the range by x columns, in our case the COUNTA will count the number of lines in the first ROW, it really isn't complicated to understand.

One thing to take into account is that we assume that column 1 will always have data in it for every row of data, and that the first line contains headers which will always have data for columns.

[nmartinet-blog]

Multiple Criteria Lookups

I already showed you how to create different types of lookups with a combination of MATCH and INDEX.

You can create a multiple criteria lookup by using the same type of formula. The basics of it are to concatenate the values you look for and search for them in a column concatenating the lookup columns. You can also do this without having to create extra columns/rows by using and array formula:

=INDEX(A1:D11, MATCH(A4&C4, A1:A11&C1:C11,0),4)

Don't forget to enter it with CTRL+SHIFT+ENTER or it won't work. You can also use index and and match to find the columns to concatenate, or the value to return. For example:

=INDEX(A1:D11,MATCH( A4&C4, INDEX(A1:D11,0,MATCH("Name", INDEX(A1:D1,0,1,1), 0),1) & INDEX(A1:D11,0,MATCH("Country",  INDEX(A1:D11,1,0,1),0),1),0),MATCH("Email", INDEX(A1:D11,1,0,1),0))

It's a lot of idexes and matches, but it gets easier to manage if you format it properly and change the ranges to named ranges.

[nmartinet-blog]

Advanced Excel Lookup

H/VLOOKUPS are really usefull formulas, but they're not the most flexible formulas. But by using combinations of index and match, we can create different more flexible lookups.

Looking up a value at the left of the column with the value:

=INDEX([TABLE], MATCH([LOOKUP VALUE],INDEX([TABLE],0,[LOOKUP COLUMN],1),0), [VALUE COLUMN])

Just replace the bracket tables with the values you need - for example:

=INDEX(A1:D11, MATCH("Canada",INDEX(A1:D11,0,3,1),0), 1)

will look for canada in column3 and will return the value from column 1.

H/VLookup - To find the value of the intersection of a VLOOKUP and an HLOOKUP.

=INDEX([TABLE], MATCH([VLOOKUP VALUE],INDEX([TABLE],0,[LOOKUP COLUMN],1),0), MATCH([HLOOKUP VALUE],INDEX([TABLE],[LOOKUP ROW],0,1),0))

For example

=INDEX(Data,MATCH("John",INDEX(Data,0,1,1),0),MATCH("Email",INDEX(Data,1,0,1)))

Will give find the the intersectino of John and Email.

[nmartinet-blog]

Troubleshooting excel cell locking

TL;DR - If come sell are not locking correctly after protecting a sheet and the cell is locked in the cell property dialog, check in 2003 - Tools -> Protection -> Allow Users to Edit Ranges 2007/2010 -> Review -> Allow Users to Edit Ranges And remove all the ranges in  the dialog.

If you try lock a sheet and some cells are still editable, and the cells are really locked in the cells properties, your problem probably might be having ranges in "Allow Users to Edit Ranges".

In excel 2003, go to Tools -> Protection -> Allow Users to Edit Ranges

In 2010 go to Review -> Allow users to Edit Ranges

Which in both versions gives you this dialog

Just delete the ranges and the cells should lock correctly.

Usually this feature allows you to define different ranges that can be unlocked by different people so that they ca edit the cells, each range can have different passwords, or the ranges can be unlocked by the user name or user group. The cells where not locking properly because they where allowed ranges, but they did not have a password set.

Keep in mind that the ranges in this dialog are different from the named ranges defined in the Name Manager. The named range can be used when defining the unlocked ranges, but they get converted to the range references, so changing the named range afterwards will not change the unlocked range, so the two sets of ranges have to be managed separately.