Tips – 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!

How to copy budgets

Today’s #TipTuesday comes to you courtesy of a question on the GPUG open forum about how to copy a budget in Dynamics GP.

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

SmartList Tips – search

Today’s #TipTuesday article is yet another SmartList tip. The past few weeks, I have been focused on small aspects of SmartList since I love this tool so much! Today I’ll be talking about searching & filtering in SmartLists.

SmartList Tips – favorites

Today’s #TipTuesday topic is still related to SmartLists, the subject I’ve been writing a few of these about the past few Tuesdays! My topic is SmartList Favorites, which will be a bit shorter than the others as there is only so much one can write about it!

PS, unrelated side note: being Canadian, it’s killing me inside that I have to keep typing “favorites” instead of “favourites” but I know for SEO and all that stuff if users are searching for the proper term with respect to SmartLists they will be looking for the US spelling. You win some, you lose some, eh? 😉

SmartList Tips – working with columns

Today’s #TipTuesday post is the third in my series of tips on using SmartLists in Dynamics GP. The first two posts were around the “basics” as I call them and then printing and exporting. Today’s topic is working with columns.

SmartList Tips – printing & exporting

Today’s #TipTuesday is a continuation from last week, where I covered some of the basics around SmartLists: terminology, security, sorting & resizing columns, using the GoTo, and statuses.

In this post I plan to cover a few more things that I didn’t fit: more of the commands on the toolbar (Refresh, Stop, Print, Excel & Word) plus a bit more detail around exporting to Excel.

SmartList Tips – the basics

Today’s #TipTuesday is another one for the newer Dynamics GP users in the community. Recently a user asked me how they could prepare a SmartList for some receivables data they wanted. It turns out they had really not utilized SmartLists at all in their day to day work yet, despite using Dynamics GP for a few years. It occurred to me how little time we spend training users on being more effective with this valuable tool!

Over the next few weeks, I will post different tips about how to utilize SmartList more effectively!

Show Required Fields

Today’s #TipTuesday is simple but for new users, it could be a very helpful one. This post is all about options to make “required” fields in Dynamics GP more visible to users, a feature that is easily overlooked in training.

1 2 3 4 … 9 10 11

Tag: Tips