Roster Automation

This blog will go over some automation techniques for Excel, Outlook, and Adobe PDF, specifically as they relate to the credentialing world.

[automatemecapn]

A short update - I am in contact with Tumblr Support about my disappearing posts. It seems the issue may be the plaintext JavaScript in the body. If they are unable to resolve it, I will attempt to include the codes as images as a workaround and attempt a different platform. I simply chose Tumblr because I've used it off and on for various projects for about 15 years, but I'm willing to become familiar with a different platform if necessary.

[automatemecapn]

Working With Rosters

Now, we’re going to fill in our rosters, and it’s going to be straightforward because of the names we set. For example, in the field we need the CAQH ID, we can just enter “=CAQH”. If you find a field you have not already covered on the Provider Data tab, add it to the bottom and assign it a name.

Returning to our Hospitalist example above, let’s say a roster asks if a provider is a hospitalist or not, and wants the field filled out “Y” or “N” depending on the answer. For that, we can use “=IF(Hospitalist,”Y”,”N”)”. Because we used the formulas of True or False in this cell, it becomes a very short formula.

There will be other fields where you will have to conform to the formatting of the payor roster and it might not exactly match what you have. We can fix some of these with more IF functions, some of them can be fixed by formatting the destination cell. For example, if you store your TIN as 000000000 but the form wants it to be 00-0000000, you can fix it with a custom number format.

At this point, you can stop if you like. Select the entire roster in your Master file to copy it and paste it as values in the sheet provided by the payor, which you can access quickly using the links you set up in the previous lesson. The VBA coding to send this roster with 1 button will come in a future lesson.

Why is it important to paste as values? If you paste normally, it retains the formulas. When the payor receives the file, those formulas will break because they won’t know where to find the information anymore. Pasting as values means we paste what the cells show instead of the formulas.

[automatemecapn]

Preparing the Master File

We will be going from the easiest to the most complex of the automations.

Create a new workbook and include copies of each of the roster files in separate sheets. I named the tabs after the payors to make them easier to identify. You will also need a tab with the export from your credentialing software (if you have it) or a standardized Excel sheet containing the information on your providers. I labelled the tab with the export data “Provider Raw”, but you can name these however you like. Any time I reference Provider Raw, it will refer to the credentialing software export tab.

Open the tab containing your exported software data, highlight the entire roster, and format it as a table. I named this table “Providers” to make things easy for myself. When you need to update this, paste the new export as Values.

Why do we format this as a table? This is so that we have finite ranges that will automatically expand when new data is added for XLOOKUP to reference later on. With finite ranges, your workbook will run much faster.

Why do we paste as Values? This way, we don't accidentally include formulas that may refer to cells or functions that could be broken during the copy/paste action.

Our credentialing software does not include all the information the rosters request, so I made an additional sheet named “Provider Data” where I can input that information. This is also where all the XLOOKUP functions to come will populate. I also made an additional sheet where the data for each practice can be kept centrally called “Practice Data”; this sheet should also be a table.

Coming back to “Provider Data”, we’ll start with 2 columns. The first I’ve labelled “Field” and the second “Value”. Under Field, start adding the fields you will need to add to each roster, such as Name, Practice Name, NPI, TIN, etc. As you do so, Name the corresponding field in the Value column something easy to remember, such as NAME, CAQH, OFFICE_PHONE, etc., using the Define Name option in the Formulas menu in Excel. We can keep adding to this as we need to. In my file, there are currently 73 rows in this.

Why are we using Names? This will make it a lot easier to link back to this information when we get to the rosters. Instead of trying to remember the individual cells like B2, B3, etc., we can shortcut them with NAME, CAQH, etc.

Why are we even doing “Provider Data” as an intermediate instead of pulling directly from the exported data itself? This way each XLOOKUP function only needs to run once instead of every time for each instance. This will make the workbook run faster.

Hopping over to “Practice Data”, we’ll make another table (I called mine “Practice”). Each practice should have its own row, with columns with the information needed like Name, TIN, Physical Address, Suite, etc. You can add additional columns as needed and the table should automatically expand unless you’ve turned that setting off. Assign a name to a column that would uniquely identify the practice. I chose the Name column, so the reference to it would be “=Practice[Name]” and I named it “P_Name”.

I repeated this step in the Individual NPI column of the export data table, calling it “All_NPI”.

Returning to “Provider Data”, I assigned Data Validation to the Practice value, where it populates a dropdown list that pulls from “=P_Name”, and did the same thing with the Individual NPI value, but with it referencing “=All_NPI”.

Why do we do this? XLOOKUP works best when there is an exact match and this will force us to input an exact match either by selecting from the dropdown (like I do for Practice Name) or by pasting what we’d like to input (like I do for NPI). If there is not exact match, there will be an error and you will not be able to proceed until you work it out.

Now we start adding all the XLOOKUP functions we’ll need. Reference either the Practice Name or NPI to return the information you need from either the export data or the Practice Information tables. For example, to pull up the last name of a provider, the formula I’ve used is “=XLOOKUP(NPI,All_NPI,Providers[Last Name])”.

If neither table will have the information you’ll need, you’ll need to input that data manually. For example, my credentialing software does not have a field for Supervising Physician, so I’ll need to input that manually every time. I recommend highlighting any fields that do not use the XLOOKUP functions a color such as yellow so you’ll remember to update them when loading a new provider. If you like, you can also filter that section by color so that the auto-populating fields are hidden from sight and you can go straight from highlighted cell to highlighted cell.

Sometimes these fields are a Yes/No question, such as if the provider is a hospitalist or not. For those, use “=TRUE” or “=FALSE” for yes and no, respectively. I’ll use this example further down, so I’ll name this field “Hospitalist”.

Why do we use =TRUE and =FALSE? It makes functions easier down the line, and I’ll explain more further down.

Finally, it is advised to make a tab where you'll list your payors, which I've named "Payor Summary". In my file, I have columns with the payor names, their method of submission (form, roster, web portal, etc.), the relevant address to either a URL or the file location where their separate template file is, and a column where these links are made clickable. Later on, we'll also include buttons we can click to run our automation scripts as well.

Why do we have separate columns for the address and the links? This way, our automation scripts can reference these cells as well. When those addresses change for any reason (the payor changes their portal URL, releases a new template, etc.), you only need to update the address to update all related functions.

To make the clickable links, we'll use the HYPERLINK function. If the payor names are in column A and the addressed in column C, the formula would be "=HYPERLINK(C2,A2)", and you can copy and paste that formula down the entire column to repeat it for all the payors. When you click the cell containing the hyperlink, it will open the URL or file referenced. To improve readability, you can hide the addresses column and unhide it whenever you need to update it.

At this point you should have:

One Excel file with several tabs containing your rosters, credentialing software export, a payor summary, and a tab where all the XLOOKUP functions live

At least 2 tables in this file

Many named fields to reference back to for the next step

[automatemecapn]

Introduction

This blog will go over some automation techniques for Excel, Outlook, and Adobe PDF, specifically as they relate to the credentialing world.

As a disclaimer, I am not a programmer. Many of these techniques were stumbled on by trial and error, and a lot of Googling. I might not be able to break down what all parts of a given VBA or JavaScript code do. If it's not a regular Excel function, I likely did not go into this knowing what the formulas do. I'm also not a teacher and how I'm presenting this information is already broken down into the easiest presentation I can think of. There may be better or more efficient ways to accomplish the things I'll be presenting; I'm simply sharing what has worked for me.

This blog also works on the assumption that the reader has some experience with basic Excel functions already or is willing to do the research on their own to work them out. Similarly, the reader should be familiar with form building on Adobe PDF if they intend to use these techniques to populate Adobe forms, though I will explain some of this in my modules.

I might be able to answer a couple questions, but the expectation is that the reader will do their fair share of stumbling through to figure out how to apply these to their own situation. I will not be answering so many questions that I'm essentially doing the work for someone else. Simply put, this blog is as much work as I'm willing to do for free on the matter. If a lot of assistance is needed, it can be provided on a fee basis, including doing the work in making the template for the client.

Prerequisites

Excel functions

XLOOKUP

Named cells and ranges

Tables

Data validation

Different paste modes, especially Paste as Values

Formatting numbers

Adobe PDF

Form building

Importing and exporting field values

Shortcuts to know

Alt+F8 Opens the Macros list

Alt+F11 Opens VBA editor

Alt+Q Exits the VBA editor and returns to the workbook

To start with, you will need the following:

Your roster as you have it or how it exports from your credentialing software

The rosters your payors use

The PDFs your payors use

A version of Excel with VBA enabled