Microsoft Office Forums Trying to create an invoice by cutting and pasting data from one excel speadsheet to another

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-15-2015, 09:51 PM
famres famres is offline Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Windows 7 64bit Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Office 2010 64bit
Novice
Trying to create an invoice by cutting and pasting data from one excel speadsheet to another
 
Join Date: Mar 2015
Posts: 4
famres is on a distinguished road
Question 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!
Attached Files
File Type: xlsx payment report sample.xlsx (20.5 KB, 25 views)
Reply With Quote
  #2  
Old 03-17-2015, 04:55 PM
charlesdh charlesdh is offline Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Windows 7 32bit Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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.
Reply With Quote
  #3  
Old 03-18-2015, 05:23 PM
famres famres is offline Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Windows 7 64bit Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Office 2010 64bit
Novice
Trying to create an invoice by cutting and pasting data from one excel speadsheet to another
 
Join Date: Mar 2015
Posts: 4
famres is on a distinguished road
Default

Quote:
Originally Posted by charlesdh View Post
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.
Thank you so much for responding,
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!
Reply With Quote
  #4  
Old 03-19-2015, 03:30 AM
Snakehips Snakehips is offline Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Windows 8 Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Office 2013
Advanced Beginner
 
Join Date: Mar 2015
Posts: 36
Snakehips is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 03-19-2015, 02:10 PM
charlesdh charlesdh is offline Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Windows 7 32bit Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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?
Reply With Quote
  #6  
Old 03-19-2015, 04:12 PM
Snakehips Snakehips is offline Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Windows 8 Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Office 2013
Advanced Beginner
 
Join Date: Mar 2015
Posts: 36
Snakehips is on a distinguished road
Default

Quote:
Originally Posted by charlesdh View Post
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?
Charlie,
???? 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
Reply With Quote
  #7  
Old 03-19-2015, 04:45 PM
charlesdh charlesdh is offline Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Windows 7 32bit Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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
But in the other sheets you cannot find the values displayed.
And the other question is "Is the data displayed line for line in the other sheets?"

To me the math does not line up.
Reply With Quote
  #8  
Old 03-19-2015, 07:35 PM
famres famres is offline Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Windows 7 64bit Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Office 2010 64bit
Novice
Trying to create an invoice by cutting and pasting data from one excel speadsheet to another
 
Join Date: Mar 2015
Posts: 4
famres is on a distinguished road
Default

Quote:
Originally Posted by Snakehips View Post
Charlie,
???? 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
Thank You for responding! I feel like I am starting to get CLOSER to the solution I am looking for. I was thinking that there must be some formula, code or whatever that can do this work for me so that I can avoid human error in translation.I think you understand what I am wanting to do.... Basically I do want to be able to have some way that the data ($ amounts) listed in column O of the "Applied Payments Report" sheet can reference a table like the one I sort of did called "Adjustment Amount" and be able to then automatically input the "adjusted payout amount" for each listed applied payment ....in place of the Payment Amount from the original spreadsheet
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
Reply With Quote
  #9  
Old 03-21-2015, 05:59 PM
famres famres is offline Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Windows 7 64bit Trying to create an invoice by cutting and pasting data from one excel speadsheet to another Office 2010 64bit
Novice
Trying to create an invoice by cutting and pasting data from one excel speadsheet to another
 
Join Date: Mar 2015
Posts: 4
famres is on a distinguished road
Default

Quote:
Originally Posted by famres View Post
Thank You for responding! I feel like I am starting to get CLOSER to the solution I am looking for. I was thinking that there must be some formula, code or whatever that can do this work for me so that I can avoid human error in translation.I think you understand what I am wanting to do.... Basically I do want to be able to have some way that the data ($ amounts) listed in column O of the "Applied Payments Report" sheet can reference a table like the one I sort of did called "Adjustment Amount" and be able to then automatically input the "adjusted payout amount" for each listed applied payment ....in place of the Payment Amount from the original spreadsheet
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
I am so close yet so far away..... Thank you again for the v lookup formula above. I was able to copy the code and then highlight and select the one cell (O7) and insert the formula which converts the original $47.50 that was in that cell to the adjusted amount of $37 (which I see it retrieved from the table I put together)......BUT when I attempted to highlight the ROW of cells from O7 to O25 and then paste the formula....or paste the formula into other cells in that row....it gives me an error (something about merged cells I think?).....I am obviously doing it all wrong?? I dont understand how to get all the cells from the "APPLIED PAYMENTS" row to look up the amts in the "Adjusted Pay AMOUNTS table"? Please Help
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 06:04 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft