Not every post has to be about advanced concepts!
To turn off the “Do you want to update links” prompt from popping up every time you open a spreadsheet (you can still update links manually):
Edit | Links…
Lower left corner, click on “Startup Prompt…”
Select “Don’t display the alert and don’t update automatic links”
OK

Posted in Excel No Comments »

If you want to reference a range whose size changes (for pivot tables, for example)
Insert|Name|Define…
Name the range, and in the refers to box:
=OFFSET(’Sheet Name’!$A$1,0,0,COUNTA(’Sheet Name’!$A:$A),COUNTA(’Sheet Name’!$1:$1))
the $A$1 is the upper left of the range (change as needed)
The first “COUNTA()” is the number of rows, and the second “COUNTA()” is the number of columns.
THIS ASSUMES THAT [...]

Posted in Excel No Comments »

Regular Expressions are very powerful pattern matching tools that can be used to find, return, or even substitute string patterns.
To use these, you’ll need to enable the VB Reg-ex Library:

In Visual Basic Editor - Tools|References
Select “Microsoft VBScript Regular Expressions 5.5″

For Help with Regular Expressions, Google is your friend, but you might start here:
http://www.regular-expressions.info/reference.html
Here are a [...]

Posted in Excel No Comments »

I’ve found this useful when working on files on a department shared drive. I link it to a custom button on my toolbar, so I can just click the button and copy the full filename to the clipboard.
Sub copyFilePath()
‘ Sub copyFilePath
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 02/20/2007
‘ Source:
‘ http://www.cpearson.com/excel/clipboar.htm
‘ Copies path [...]

Posted in Excel No Comments »