Excel – JenKuntz.ca

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!

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!

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.).

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.

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!

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.

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.

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!

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.

Excel tip: Copy & Paste Unique Values

Here’s a cool little Excel tip for #TipTuesday. Have you ever had a column of information in Excel and wanted to get a distinct / unique list of values from one of your columns of data? I learned a new thing last week doing just this, and, surprise, it wasn’t “create a pivot table with that column”!

Continue reading “Excel tip: Copy & Paste Unique Values”

1 2

Tag: Excel