Excel Spreadsheet

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.