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