Today’s #TipTuesday is an odd one. A few months ago, a client had an issue with a specific Excel workbook they couldn’t “open”. Of course, the usual questions around “is there an error message?” etc. didn’t pan out. Typically it might be a corrupt file issue or something, but once I saw the issue on their computer, I realized what the problem was. It turns out someone accidentally “hid” the workbook and then saved it for the next unsuspecting user. To the user, it was akin to voodoo, something appears to “open” but nothing is visible.
Continue reading “Excel – where did my workbook go?”Tag: Excel
Excel Background Refresh & Pivot Tables
Welcome to my first 2020 #TipTuesday post. Today’s topic is Excel and how a background refresh setting can impact refreshing pivot tables.
The background to this post is related to a report I was creating for a client to validate timesheets prior to approval. The report has a couple of queries in them (from SQL) and a pivot table to summarize the data. In order to make the refreshing easier, I created a tiny bit of VBA and a button for the users to click when they wanted to refresh the data, to pull in the latest timesheets. When testing it, it was doing everything except updating the pivot table. Here’s how I resolved that!
Continue reading “Excel Background Refresh & Pivot Tables”SmartList Export Options
Today’s #TipTuesday post is the last in my mini-series of SmartList tips for Dynamics GP. I’m going to walk through a very simplistic example of an export from an Account Transactions smartlist, to Excel, where a macro will do some basic formatting and create a formula for Net Amount.
The method I show below is just one way to do this, and it’s a mini end-to-end example that you can follow if this is your first time using this feature. The intent of SmartList Export Options is to automate repetitive tasks. If you export SmartLists a lot and continually do the same things to the exported file, then read this post to see how you might be able to automate part of your work with that particular export. While the example is simplistic, the options are fairly limitless with what you could do with VBA in Excel!
Continue reading “SmartList Export Options”Excel tip – is X in the list?
Today’s #TipTuesday is a detour from my recent SmartList tips for Dynamics GP, and I’m going back to Excel for this one.
I have been working on a project recently where some data validation is needed to ensure that user-entered data on some forms is valid. I want to have some visual validation for Accounting when reviewing these forms prior to uploading the data into Dynamics GP to determine in advance if the G/L Accounts exist, or if the Job/Project and/or Cost Code/Category exists. In my use-case, I can’t use the built-in Data Validation features as the lists would be too lengthy to be manageable, so the fields are simple text fields, prone to errors and typos.
I want an easy way to highlight invalid entries before proceeding further in the process, during the review stage. Specifically, I want to use Conditional Formatting to highlight anything that can be validated if it isn’t in various lists (of accounts, jobs, cost codes etc.).
Continue reading “Excel tip – is X in the list?”Excel Navigation tips
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”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”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”Fun with XML, the end
(At least I hope it’s the end, LOL!). Here are the various parts to my mini-series so far, which is the story behind all of this (part 1), the technical deep dive into the SQL query (part 2), a bit about namespaces in the FOR XML (part 3) and finally a bit about validating an XML file against an XSD schema (part 4).
So, what is this post about? This is taking a step back to describe the process itself. The part 1 story tells you what I’m doing, and ultimately I am all about the process. It’s one thing to create some code in order to create an XML file, but ultimately the best solution for an ongoing, repeatable process is to think about the end to end steps from the point of view of the user. This is about the “solution” that I created, which may give you some ideas on either the thought process behind it, or ideas on how to mimic the same thing yourself for a process of your own on something else.
Continue reading “Fun with XML, the end”