Excel Spreadsheet

Copper Contributor

Hello! This is my first post, and I am desperate for some guidance. I work in the billing/accounting office at a senior living facility. I have been tasked with taking the resident salon charges and turning it into a spreadsheet, using a specific format. The format was provided by our EMR so that we will be able to import the charges in directly, rather thank keying in each charge individually. Below is the formatting provided for the spreadsheet, and also an example of the salon charges that I would be using to create it.

spreadsheet formula.PNGsalon charges.JPG

2 Replies

@Kdehav 

 

I think you need to go back to whoever gave you that ridiculous set of "specific format" guidelines and ask how it relates to the sheet of salon charges. And how it relates to a putative Excel spreadsheet.

 

Why do I say that? 

  1. The first two columns in the Specific Format Guidelines refer to info that's not on the Salon Charges
  2. There's no mention of "Resident's Name" in the guidelines
  3. There's no mention of "Service" (although one might assume that Billing Code or Description/Comment could be used
  4. Equally important to you and anybody who might wish to help, this looks like Format Guidelines from the old card punch days of computer, referring as it does to Start and End Positions; certainly not spreadsheet specs. 

@Kdehav 

You did not provide us with enough information to produce a complete solution, but we can get you started. The person who gave you the formatting document perhaps did not provide you with enough information, but that's sadly typical in process design.


(And you do not mention which version of Excel you are using. My example file uses the Excel function TEXTJOIN, which is available only on Excel 365/Excel for the web/Excel 2021/Excel 2019. Rework would be required for older versions.)


For example, there is a note in the upper right of the formatting document about delimited files. Creating delimited text (rather than fixed-position text) is less work. But what characters are allowed as a delimiter? In my attached example, I have used a semicolon.


And if the EMR software can read data from a spreadsheet, it may not even require formatting into either fixed-character-column text or delimited text; perhaps you can just arrange the necessary fields into separate spreadsheet columns. But you will need to discuss/review this with your EMR contact.


In the data entry spreadsheet (worksheet, in Microsoft terminology; in the following I will refer to "worksheet" rather than "spreadsheet"), the user(s) should enter the date of salon service for every row, not just for the first service of the day. (Otherwise, complications can arise.) The Resident's Name appears to be not required for this worksheet, but it will be useful in spotting any discrepancies between the handwritten form and the worksheet.


The presence of "Profile / Employee ID" in the formatting document suggests that your data entry worksheet needs to capture the identity of the person who is providing the service. Therefore I included an Employee column on the DataEntry worksheet.


Both the type of service and the identification of the employee must be precise (in order for formulas to translate those to related billing codes/employee IDs), so my example includes data validation for those two columns, in the form of in-cell dropdown lists. Information on how I did that is shown in this article. You will need to modify those lists and their associated values with real data; see the Reference worksheet. And when you add/remove entries, resulting in a different number of rows for a list, you will need to change the arguments to the VLOOKUP functions that reference these lists. (There are alternatives to this extra work, like dynamic named ranges or Excel tables, but those would complicate the solution before you are ready for that.)

 

The exportable data is calculated in a set of formulas currently in column G. Depending on your requirements, that might be usable as-is. Or, e.g., you might need to filter it to a range of dates; for that, see worksheet DataToExport. It uses a single formula (in cell A2) with the FILTER function to copy a subset the values from the DataEntry worksheet.

 

This work is incomplete (see the _Info worksheet for more notes), but that should get you started. Your second image quality was poor and/or my eyesight is declining, so I entered only some of those rows of data.