|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trying to create an invoice by cutting and pasting data from one excel speadsheet to another
I would appreciate any help or suggestions you may have.
I am trying to cut and paste data from one speadsheet to a second spreadsheet that can then take the data that is in one of the columns and "populate" pre-set adjusted amts that correlate directly to the $ amts listed in one of the columns on the 1st spreadsheet. There are a total of 10 different possible $ amts that could be listed on the 1st spreadsheet and 10 corresponding "adjustment amts" that would need to be populated on the 2nd spread sheet..... Basically, we currently CREATE an excel payment spreadsheet from our billing software that lists dates, services performed, and amt of $ that Insurance Co paid US for services performed. I am needing to create an Invoice/Spreadsheet with the exact same info...EXCEPT that the "payment amt from Insurance Co" would need to be "translated into the "adjustment amount" that is being Paid OUT to our contract employees. I have not been able to figure out if this is even possible in Excel or if I need to use a completely different program. We were using Quickbooks and manually entering in the service items to create the "adjusted payment invoice"...but have had too many issues with human error and have been losing money. Please help! I am at a loss and have spent way too many hours trying to figure this out I have attached a sample speadsheet that we would create from our billing software, an example of what I am wanting to include in my excel invoice (highlighted yellow) plus the adjusted payout amts (highlighted green)....and would NOT want the invoice to print the greyed out column that shows the Insurance payment amt. Thank You! |
#2
|
|||
|
|||
H,
Not really sure what you want. But, if you want to print "Invoice Warning" you can hide the column you do not want to see. Can you tell us you cut what form which sheet and paste it to which sheet? You have Green in sheets Warning and Adjusted. Do you want it in both? If I knew how you can use formula for you file. No need for a code. I"m sure someone on the forum knows how to do this via a formula. But, I'll look at it from a code view. |
#3
|
|||
|
|||
Quote:
I dont think I did a good job of explaining myself. The Insurance Payment Report is a report that is generated from our current billing software. I am wanting to be able to use this spreadsheet to create an invoice that we can provide to our contract therapists showing the same information from Insurance Payment Report that is in columns highlighted in yellow (basically the names of clients that were seen/the DOS/the PCT)....I want to use the data from the yellow "Applied Payments" column to be able to automatically populate a corresponding "Adjusted Amount" that will be paid out to the therapists (This would be the GREEN Column that I added to the "Invoice Wanting" spreadsheet. The third speadsheet is a table of the current amounts that we pay out when we are paid a set amount by insurance....for example when Insurance pays us $47.94 as listed in the "Adjusted Payment " worksheet...we pay the therapist $37....so I would want for this to automatically populate on the "Invoice Wanting" spreadsheet. If I cut and past the data from "Insurance payment Report" to "Invoice Wanting" my HOPE is that there is some way that the $ amounts listed in column P can somehow reference the "Adjusted Pay Amt" table from 3rd spreadsheet and automatically populate column R with the amount that we need to pay therapist. I would not want the data from Row P (applied payments) to be visible on the "Invoice Wanting" spreadsheet. I hope this may be a little clearer now? Thanks! |
#4
|
|||
|
|||
famres,
If you are happy to create the invoice 'manually' on a copy/paste basis then maybe this will help. Given your attached file's sheets..... Firstly, just as a one off, in the adjusted payments sheet clear B6 and re-enter the value 34.57 Enter the formula =LOOKUP(ABS(O7),'adjusted pay amts'!$B$6:$C$15) in cell Q7 and drag it down to Q25 That will look up your adjusted payments. You can then hide column O which has the Applied Payments so that it will not appear on printed invoice. Otherwise to do away with column O in the invoice area. Copy the Applied Payments to say column AA which is outside your print area then change the LOOKUP formula to =LOOKUP(ABS(AA7),'adjusted pay amts'!$B$6:$C$15) If you always have the same max number of line items (19) then you can leave all the formulas permanently in column Q. Otherwise find a routine that suits. Hope that helps. |
#5
|
|||
|
|||
Hi,
The data you have in sheet "Adj" does not correlate with the info you have in the other sheets. Also how do you know in the "Adj" who the data belongs to? |
#6
|
|||
|
|||
Quote:
???? If you mean the table in the 'adjusted pay amts' sheet then surely that is intended as a lookup table to convert set amounts received from insurers to set amounts payable to contractors ??? famres, Just to clarify on my previous post. The formula is tho be in O7:O25 of the 'Invoice Wanting' Sheet. If the row number for each line item in 'Invoice Wanting' will always be same as the row number in 'Insurance payment report' then you need not copy /paste the Grey Applied Payments to Invoice wanted. Just use the formula ..... =LOOKUP(ABS('Insurance Payment report'!O7),'adjusted pay amts'!$B$6:$C$15) in Invoice Wanted O7 |
#7
|
|||
|
|||
Hi,
When I look at "Adj" sheet I can see for example Code:
PAYMENT amt from INSURANCE adjusted AMT WE PAY Contractor 34.57 22.00 And the other question is "Is the data displayed line for line in the other sheets?" To me the math does not line up. |
#8
|
|||
|
|||
Quote:
but I am clueless how to do actually do this . the sheet called "Invoice I am Wanting " was uploaded just to help illustrate the information I am wanting to include on an Invoice sheet created from the excel spreadsheet that I generate from my billing program on a weekly basis. I vaguely understand the table concept but dont quite understand where Im supposed to put it to reference it and the correct formatting? As far as the # of line item (rows) being the same every week....they will not. the invoice could have as few as 2-3 line items if only 3 clients were seen....and as many as 40 line items (or 40 clients seen) ....however the # of line items on the Payment Report will match the "Invoice I am Wanting" report. There are basically 10 different possible $ amounts that the Insurance Co may pay us...and there are 10 different Adjusted Payment $ amounts that we will be paying out to our contractors depending on the amt that we get paid. Actually there is really 11 if you count $0 as an amt. The payout amt would also = $0 of the Insurance paid us $0 . I am wanting to do away with having to manually enter the service items into an invoice manually because historically we have has lots of errors which have hurt us financially (usually paying out too much) The other possible way or creating an invoice from the Payment Report would be to have a table that references the CPT codes in column J, The Insurance Co's listed in column V, and Location codes that could be generated by including an addition row on the Payment Report. These 3 pieces of information would determine the "Adjusted Payment" amount I am needing to populate ...based on how the three are combined...for example... I client that has PCT of 90837....Insurance Co is Cenpatico Behavioral Health....and POS (column that COULD be included in original report) of 12 = $70 paid to contractor.....while a PCT of 90837 / Insurance Cenpatico Behavioral Health/ POS 11 would = $52 to be paid as the adjusted rate to contractor. I am not sure if that way would be MORE complicated or EASIER? It seems like like this could be a perfect solution if I could just understand exactly how to apply the formulas Sorry for being so green...I appreciate everyones help and patience |
#9
|
|||
|
|||
Quote:
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copying and pasting acount data into Excel as text will not reformat to a number or currency | ncbergst | Excel | 3 | 09-04-2014 11:29 AM |
Columns resize badly after cutting and pasting rows | mjs9876543210 | Word | 2 | 02-12-2014 03:03 PM |
Cutting and Pasting and Macro problems in Excel 2010 | enkel | Excel Programming | 3 | 01-02-2012 10:15 PM |
Generate Excel Speadsheet from Access, Web, or??? | chrisalter | Excel | 0 | 07-12-2011 01:04 PM |
Pasting table in Photoshop cutting off table | azdolfan | Word Tables | 0 | 05-16-2010 01:52 PM |