This is my online notepad of tips and tricks. I'm posting it publically in case it helps others. Main site: ...
I've noticed that it takes my colleagues ages to find a specific email, because they leave absolutely everything in the inbox, treating it as a giant unsorted archive and to-do list. My method is to leave nothing in the inbox (for longer than it takes for me to pop it in another folder). I have 3 main folders: To do: emails that require me to do something. FYI: emails that don't require me to do…
These command will affect all the files in whichever folder you're currently in (inside bash). Remove string from all filenames I decided to add this to my online notepad because I use it a lot. When ripping CDs or buying mp3s, the tracks often come out with the artist name and album title in every single filename, which I don't want. Instead of editing them all one-by-one, I looked into how to…
Aliases In Powershell, you can set an alias (custom command) for a session by typing it directly: function thanksbeef { echo "You're welcome, beefsack." } Set-Alias -Name thankyou -Value thanksbeef This is a simple function: type thankyou and the shell responds with You're welcome, beefsack. This behaviour will stop as soon as you exit or restart Powershell. Create a file with permanent aliases…
If you have a bunch of files that you want to combine into one zip file, but they're scattered in different folders, the only way to do that in File Explorer is to first copy all the files into one folder, then zip them from there. It's tedious and made me think there must be a better way. It turns out there is; you can use Powershell to zip the files and not have to touch the files themselves.…
Jump to macro I found the base macro in this MS Office Forums thread . This post is to add my own usage tips; hopefully they will save you from the same trial and error I went through. Assumed knowledge: How to add a VBA macro to a Word document. How to set up a mail merge using an Excel file as the source data. Notes: The table column headers in your mail merge source data can't have brackets ()…
Assumed knowledge: Standard XLOOKUP . XLOOKUP with multiple search criteria Generic formula: =XLOOKUP(1,(column1=criteria1)*(column2=criteria2)*(column3=criteria3),array_to_return_data) The 1 represents 'all values true'. XLOOKUP with dynamic lookup initial value or dynamic results column This is done by nesting one XLOOKUP inside another. You can use this instead of an INDIRECT formula (which is…
Overview Assumed knowledge: How to use data validation to create a dropdown list. Tables and how to refer to them in formulas. Process: Create a worksheet with categories as columns and items as rows. (We'll automate it.) For top-category dropdown, apply data validation as a list. For the secondary dropdown, apply data validation as a list. 1. Create a worksheet of source data We want to create…
Line ^ = line start $ = line end Text between brackets \(.*?\) \( = escape left parenthesis .*? = match all . = any character * = any number of times (0 or more, as opposed to + for 1 or more) ? = optionally (i.e. catch the instances in which there is nothing between brackets) \) = escape right parenthesis Single character Wildcard . = match any single character. \w = match any single alphanumeric…