Today’s #TipTuesday is yet another Excel tip. This one is about navigation, and some tips around things I use every day when I’m working in Excel. Some of these are particularly useful if you are using Excel to get a set of data ready for an integration, which often is the world I’m living in. Sometimes I’ve integrating right from Excel and sometimes I’m working with something where I need to “save as” and I need to make sure I don’t inadvertently have blank rows or columns in my data. That, in a nutshell, is the background for this post and set of tips.
Continue reading “Excel Navigation tips”Tag: Tips
Excel’s hidden gem: IFERROR
Today’s #TipTuesday is yet another Excel tip. Can you tell I’ve been working with spreadsheets a lot lately? My blogs tend to follow my work and these last 3 weeks have been no exception. As I run into things or use something I haven’t touched in a while, I tend to blog about it!
This tip is about what I believe to be a hidden gem in Excel and that is the IFERROR formula. Over the years, I have seen many people do many funky workarounds to “pre-check” if they will get an error when there is a formula that will help you out much more easily!
Continue reading “Excel’s hidden gem: IFERROR”Excel VLOOKUP – numbers as text & vice versa
Have you ever tried a VLOOKUP formula in Excel where you’re looking up something that is numeric but stored as text, or vice versa? So annoying, when you seem to have one set of data one way and the lookup in another, right? Today’s #TipTuesday is all about helping you with a couple of formula tweaks to make this a bit easier.
Continue reading “Excel VLOOKUP – numbers as text & vice versa”Excel tip – “corner” copy
Today’s #TipTuesday post is a little Excel tip. It’s one of those things that I assume most people know it, and then I work wth someone and watch them use Excel and end up teaching it to someone. I call it corner copy but I’m sure there’s some official name!
There are 2 parts to this tip really: one is the copy via double clicking on the lower right corner and the bigger tip is the little pop-up window that gives you options on how that command is executed. That little pop-up window is the *gem* of this tip in my opinion as it appears in numerous instances of doing things in Excel.
Continue reading “Excel tip – “corner” copy”Dynamics GP & Office 365
Today’s #TipTuesday is a post about Office 365 and Dynamics GP and is specific to email functionality such as emailing Purchase Orders, EFT Remittances and Sales Documents. Rest assured that most other functionality other than email seems straight-forward and works no matter what (like exporting from Smartlist). (At least that’s my experience although there may be exceptions I am unware of!)
Continue reading “Dynamics GP & Office 365”Outlook credentials issue
A little while ago, I was trying to add one of my Office 365 email accounts back to Outlook and it wasn’t working. I forgot that I needed an app password because I have MFA (Multi-factor authentication) enabled on my domain/tenant. I initially put in my “regular” password and was expecting it to kick back and let me try again; however, it didn’t do that.
Continue reading “Outlook credentials issue”Excel Paste Special – Operations
Today’s #TipTuesday is an Excel tip. Did you know there are some funky Paste Special options beyond the basics like Paste Values, Paste Formats etc.? I find if you were to ask most Excel users what Paste Special is, the majority will answer “pasting values instead of formulas”. While that’s definitely true, there are also Paste Operations.
There are random circumstances where I’ve found this to be useful in a pinch and today was one of them. I was working on some Fixed Assets stuff, and my data had asset cost and accumulated amortization both as positive values. I wanted to do some Pivot Table analysis with it, to validate some Net Book Value info, without fussing with formulas. Paste Special Operations to the rescue!
Continue reading “Excel Paste Special – Operations”How to set a longer password on Office 365
This is a belated #TipTuesday, posted a day later than usual! Recently, support for longer passwords in Office 365 was announced but oddly enough, in some places the password change dialog boxes still limit you to 16 characters. Here’s how I worked around this.
Standard “password reset” feature – 16 char limit
Here’s a screenshot of a password reset window in my Office 365 tenant, and it clearly states my password must be between 8 and 16 characters. Hmm. My assumption is this will be noticed and rectified fairly quickly as it’s inconsistent with the next part I’m going to show you.
Workaround: “Forgot my password”
The current workaround is use the Forgot My Password link when you log into Office 365. If you use MFA/2FA like I do, simply cancel out of the dialog box that prompts to send a notification code and then click on Forgot my password.
Next, there may be one or two steps requiring you to verify with either an authenticator app code or text message or both depending on your organization’s MFA/2FA setup. I had to do both. Once I was past that part, the standard “choose a new password” section came up. Now I can select a password up to 256 characters.
That’s it! As I mention above, it’s very likely this will be corrected soon… but until then, this may help if you want to secure your account with a longer-than-16-character password.
SQL Alerts with HTML email body
Today’s #TipTuesday is a bit different from my normal type of post. In the course of monitoring some integrations, I had wanted to change the emails I get when there were issues. What I inherited was email alerts with attachments, without any data that I could use as an Outlook filter to tell me if something was ok or not. I had to open the attachment to see if there were errors. I wanted to find something better.
What I’m writing about today is what I’ve pieced together from various searches and unfortunately, while I got the pieces from various other blogs, I’ve changed so many things and re-worked this so many times, I no longer even have the references of those that helped me get to the point of what I’m sharing today. I wish I could link to them to thank them.
Continue reading “SQL Alerts with HTML email body”SmartList stuck on My Reports pane
Today’s #TipTuesday post is about troubleshooting an issue with the My Reports home page area/pane and how to remove SmartList favourites from there. The specific scenario here related to favourites that were “stuck” in MyReports after an upgrade to GP 2013 a few years ago. I was reminded of this when I saw this post on the Microsoft Dynamics community forum and someone asked me if I kept the scripts I used to fix this issue.
Continue reading “SmartList stuck on My Reports pane”