Creating a repetitive form without having to enter repetitive formulas into each form?

[FinGuy]

Hello,

Our practice management system has an export for excel. The export produces columns labeled Client, date, practitioner, location, and other service information. Each row is a particular service. On sheet 1 of the workbook we have created a form that collects the information we need for billing purposes. Each form is one page and their are 100 forms. When building the form it was very easy to initially create the form and format it. It was easy to reproduce the form over and over on the same worksheet. The problem is that we are having to enter the formulas for each piece of data in each individual form. This is quite the task for 100 forms. Is there a function in excel that would allow us to recreate the form with proper cell references similar to when you drag a formula?

Please understand that I have tried to search for answers, but finding the correct search criteria to get the answer i need is not working out. Hoping someone in the forum with a similar experience can point me in the right direction. Any help is appreciated!

[Keebellah]

Hi, welcome to the forum.
Without a sample I'm afraid that no one is going to be able to help you, you speak of a userform and formulas but what formulas, how are they built which one where?
I'm sure you understand what you are asking and that's the difficult part, passing all the information on to someone that has no idea of how the sheet or form looks, and assumptions won't solve it for you.
Another one, Ms-Off version 1901 ? what's that in normal Office version?

[FinGuy]

First, thanks for the quick reply! I have attached the workbook to this reply so you can see what I mean. Completely see how the issue is hard to understand. I was having trouble describing it! On the paste here sheet, is the data that we are trying to get into the forms. The forms are on the print me sheet. I have stacked 3 consecutive sheets on top of one another. Basically, each form gets certain information from a single row. We are trying to build 100+ forms stacked as we will have 100's of rows of info. We are having to enter the formulas in each form over and over again. As you will see, excel was not able to figure out the pattern of the formulas. Is their a way to repeat the form while having each form reference a single row without having to do it manually? Hope this helps explain what I am trying to accomplish and again, thanks for the help!

[Keebellah]

Yeah, I understand that it's difficult to explain
One thing, to be sure I get it:

You have one record that fills 39 rows of data, that is for one client and that you have three clients in sheet.
May I ask why you want them stacked like that? Why not one sheet per client?
Makes it easier to manipulate and you can address the the same rows in the 'forms' only the source row data varies but that could be done with formulas and you would not need macros, but, I wonder why the reason for all those sheets.
Of course if you want the sheets to be created on-the-fly and populated, then you will need a macro to do that but that is altogether another story

[FinGuy]

Sorry for the delay in response...Came down with the flu on Fri. Services are completed through the week and we run an export from our practice management system. The export is copy and pasted into our spreadsheet. The idea is that each row of information will be referenced and that data put into each form. Each row of data on the paste here sheet equals a form. We then print out the forms on the print me page. I am building the template now. I can easily replicate each form by simply copying and pasting each successive form below the last. In my spreadsheet I attached above their is only 3 forms, but I plan on having 100+ forms by the time Im done. As you can imagine, that will take quite along time to enter individual formulas in each form hundreds of times. That is what I am trying to get assistance with. I can not simply drag the formula as I would normally.

You have one record that fills 39 rows of data, that is for one client and that you have three clients in sheet. - Their are 39 rows on each form but they are all not utilized. Some are just labels. The bottom portions of each form are used after they are printed. The info collected as read from top to bottom of the form are the provider's name, client's name, id #, alt id # (when applicable), client date of birth, service date, cpt code, # of units, diagnosis code, primary insurance name, primary insurance #, secondary insurance name, secondary insurance #, tertiary insurance name, tertiary insurance #, and auth #. This is the only data being pulled from the paste here sheet.

May I ask why you want them stacked like that? Why not one sheet per client? - We stacked them so that we could simply print out multiple forms at once by going to the print sheet and printing out however many forms have referenced data in them (50 rows of data = 50 forms printed). Also, this is at the core of my issue. I am building out the template and can easily replicate the form. I simply copy and paste each successive form under the last. The issue is when I do this, excel incorrectly guesses my formulas on each successive form. It is forcing me to put the formulas into each form manually. For example, in the spreadsheet i posted, on the print me page on the first form, cell C2 (provider name) references cell Y2 on the paste here sheet. In an effort to build out my forms, I copied the form and pasted it below the last one (twice actually which is why their are 3 forms). On the next form cell C41(provider name for next service) is supposed to reference Y3 on the paste here sheet. Excel guessed that it should be Y41 on the paste here sheet. This is what I am trying to solve. How do I replicate the sheet and set it up in a way that the references for the next form reference the next row in my export data? Again, I am looking for an alternative to manually entering all of the formulas into 100+ forms.

Also, this can be applied to other exports and forms we use. It could really assist the agency in terms of efficiency. Thanks for all responses and help!

[Keebellah]

Thanks for the extensive explanation.
Let me read it carefully and see if I can do something for you.
Copying and pasting is no option, I would go for the Indirect function but, like I sai let me read and see.

[Bo_Ry]

Please try at
C2
=INDEX('PASTE HERE'!Y:Y,I2)
C5
=INDEX('PASTE HERE'!A:A,I2)
C6
=INDEX('PASTE HERE'!B:B,I2) and so on

then I2 key-in number

[Richard Buttrey]

What is the essential task here.

Is it:

1. To take information from a database (i.e. your Paste Here sheet) consisting of lots of fields of information, i.e. columns with each row recording a unique record event.

2. Pick a record (or perhaps many records) and put that data in an area on the Print Sheet.

3. Then what? Print all of the Print Sheet or only sections of it.

I'm struggling to understand why the Print Me formula refer to different rows, i.e. 2, 41, 56, 59 and several others. There seems no consistency and since there are only 18 rows in your data it's extremely confusing.

You'd be better explainaing in a narrative your overall business process and what your end goal is. I don't mean tell us how you are currently trying to get to your final result, I mean show us what the final result is by manually creating the output, and clearly explain what rules you've applied to produce the results.

I'm filled with horror when clients start talking about stuff like wanting to create 100s of something or another, when want they really need is one something that is used 100 times to create a 100 files or paper reports / whatever with a repetitive macro process - AT RUN TIME.

[FinGuy]

So I will try and explain the full process and what it accomplishes for us. First, we are a mental health agency. We have multiple therapists that provide daily service information into our practice management system. We bill in house and need specific data to bill to insurance payers. It is a short list of data that would include things like the client's name, DOB, insurance, insurance id #, etc. I must get that information to my biller. To get that information to our biller we created billing sheets. Each service gets a single billing sheet that include the info we previously mentioned. When we first started, we created a billing sheet our therapists filled out themselves. As you can imagine, it was a disaster. Handwriting issues and data not filled in properly were common issues. From there we tried to utilize excel since the practice management system added an export function. The export has all of the data we need. We just needed to put it into our billing sheet. The export itself is set up so that every row is a service and every column contains data about that service. At this point we created the billing sheet on excel which is what you are seeing on the spreadsheet I attached. The idea was to create our billing sheets on one sheet in the workbook and copy and paste the export data into another sheet. We could then set up references so that the data from the copied export would filter into the appropriate part of the billing sheet. Our idea was to set up at least 100 billing sheets on the print me sheet for our larger exports. This is where my issue is, building out the forms with correct references. I can do this manually but am trying to learn how to do this without having to manually enter formulas for each successive sheet. To reference the post by Richard Buttrey, "I'm struggling to understand why the Print Me formula refer to different rows, i.e. 2, 41, 56, 59 and several others. There seems no consistency and since there are only 18 rows in your data it's extremely confusing." This is what I am trying to fix. I made the initial billing sheet and assigned the formulas. I then copied and pasted the billing sheet right below the first but excel has incorrectly guessed which references I wanted to make. For instance, in the first billing sheet the client name ('PRINT ME'!C5) references ('PASTE HERE'!A2). When I copy and pasted to create the second form, excel guessed that I wanted to reference ('PASTE HERE'!A41) but it should have been ('PASTE HERE'A3).
So the questions is, how do I repeat the form and get cell references set up correctly without manually entering the formulas for 100 billing sheets? Also, I am sorry for the long winded response. I am trying to learn and improve as we have other forms and exports we can use. Building these out the way I am doing it now will take until 2020. I am pretty sure it could be accomplished through VBA but this is clearly beyond my skill set. I truly appreciate everyone taking the time to step in and try and help.

[Keebellah]

Based on Bo_Ry's idea I got you three records in the PASTE ME sheet.
Instead of I2 as ref I used A2 and set the reference in the cells to $A$2

You have three rows to fill in record numbers:

A2, A42 and A82

I still don't know why you're doing it this way but. good luck.
I've attached my modified file (based on Bo_Ry's)

[FinGuy]

I really appreciate everyone's help and input. Keebellah, I am doing it this way because I do not know any other way to do it. My biller needs a printed out form to bill off of, keep notes on, and track services. If there is a better way I'm all about doing it but I'm not sure what that would be. That is why I'm here. To find and learn a more efficient way to get the forms I need. The spreadsheet I attached is just a sample(there will be 100 forms when I am done building it out). I left the incorrect cell references on the second and third billing sheet so that you could see how excel incorrectly assigned references when I copy and pasted the billing sheet. I can see now that it was just confusing everyone. To be clear, the first billing sheet should collect info from row 2. The second billing sheet should collect info from row3 and so on...

[Keebellah]

Understood,
My layout works but the sheets will not be printed on for each record,
Is it an option that the biller or somebody selects the sheet or sheets (using a userform and ten prints the selected records?

[FinGuy]

Keebellah,

Is it an option that the biller or somebody selects the sheet or sheets (using a userform and ten prints the selected records? - I am open to any and all suggestions. I am not sure if I fully understand using a userform. I was under the impression userforms were for data entry. Am I mistaken?

[Keebellah]

Yes you are,. userforms are for many things, amongst that data enrty but ...
Imagination is the limit.
I'll try and put together something ans see if they can handle it.

[Keebellah]

When you want to make a selection, what do you select on the Provider?
Columns Y, Z and AA or the Patient Columns A and B?

[FinGuy]

When you want to make a selection, what do you select on the Provider? - I am not sure what you mean in terms of making a selection. The cell the provider info goes into on the billing sheet pulls the data from column Y under provider. The only thing that I planned on doing, once the template is finished, is to paste the data onto the paste me sheet and to have the information filter into the appropriate billing sheets (one billing sheet per row).

[Keebellah]

I wrote some code and added a userform.
The only option I placed now is print.
One think to consider (and I added it to) is that you must be able to select the printer where the output is to be sent to.
What I meant with select is that on the form there are three fields above the listbox and you can type in all three and the list will be filtered with what you type
You should also think about the possibility to save the output to PDF and then you can mail these files to the person or persons
But now this is what I have for you.

You selecet the name or names of the ones you want to print and then press the printer button that will appear.

If you click in the box that shows the current active printer you get a dialog to select another printer.

Have fun with it, hope it helps you.
The sheet user is the PRINT SHEET and the trick is just enter the row number from PASTE ME in cell A2 and it will show up on the sheet
When that value is 0 the formula cells as blank (Conditional Formatting)

I have not used the PRINT ME sheet

[Keebellah]

I wonder if you have take the time to look at the file and what it does.
I'd appreciate it if you did, to let me know.
Meanwhile I added the functionality to either print or export the records to separate PDF files.
I've attached the new version and one output file.

The Output's file name is editable in the VBA code (and explained in the vba code too)

[FinGuy]

Keebellah,

I apologize for the delay in response. I have been fighting a bug as I mentioned before. All I can say is wow! I can not tell you how appreciative I am for all of the time and effort you have given me. Thank you! I just downloaded the workbook and tried it out. It appears to be working perfectly. This is so far and away better than what I am capable of doing at my current skill level. I knew that something like this was possible but really had trouble trying to articulate that to everyone in the forum. The fact that you patiently stuck with me while I attempted to explain the situation is truly appreciated. I would like to ask you a couple more questions in an attempt to try and learn how you accomplished this.

In terms of the formula you used for each reference (=INDEX('PASTE HERE'!Y:Y,$A$4) - Is the 'PASTE HERE'!Y:Y portion of the formula just defining the array or area that the data would be pulled from (defines column?)? Also I am not sure I understand why the portion after the comma ($A$4), is there. Why is that added and what is its function?

In terms of the macros you enabled and the code you entered - If I enable the developer tab, will I be able to see the code you wrote?

Is it possible to rename the thread so that it would be easier for someone with a similar problem to find? If so what title would you suggest to rename the thread?

Also, if I were to want to add a select all box that would auto check all the data pulled into the user form, how would I do that?



I want to finish by saying that you have done so much to help and I don't want to take another second of your time if you don't want to give it. I am very grateful for what you have already given me. I know for the company this will be very beneficial and want to use this to expand my skill set. I also hope that this may help someone out there with a similar issue.

Thanks from a very grateful excel user!

[Keebellah]

I'm glad that you are able to use it and appreciate it.
1. The formula code is Bo_Ry's idea, I just used it.
What it does is select the column and then look for the row number you pass as parameter which in my case is the value you fill in in the cell in column A
2. You do not need to enable the developer tab just press Alt + F11 and the VBA editor opens, the code's all there
3. Renaming the thread is reserved for the Forum's moderators, so you will need to ask one of them, you can post a private message to the moderator

I have already written the code to select all in but ran into an uncontrolled look, do have the solution but wanted to wait it it worked for.
I'll try an implement it and upload a modified file later

The VBA code, when you look into it will probably read like some gibberish, but ... with patience and time and I suggest you look up some basic tutorials for starters will become clear text for you.

VBA porgramming requires at two important ingredients, time and patience and then a lot of imagination and a way of thinking out your steps in normal step-by-step actions that you 'translate' to cod respecting the syntax and only time will get you there.
I learnt it by doing and started a very long time ago, I'm 70 now and still love it

[FinGuy]

I will certainly look into some tutorials and try to learn a little at a time. I appreciate all of your input and help! I will be reminded of your help every time I use the template. Thanks! Also shout out to Bo_Ry. Thanks for helping with the formulas! Hope you all have a great weekend.

[Keebellah]

Added the select ALL option
Enjoy VBA

[Keebellah]

I did not like my quick and dirty solution for you so made some minor modifications.
Tested it and works here
Have a nice day