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
