I'm a music publisher, and twice a year, I have to calculate how much royalties I need to pay to my writers & co-publishers. This is a complex & tedious task, and to aid in that task, I have a big honkin' spreadsheet to calculate the royalties & to generate reports for the recipients. The problem today is that the formulas that worked 6 months ago now don't work. Here the deal:
I get a huge database spreadsheet with tens of thousands of records, with each record representing a single instance of royalty income. My problem is that this huge DB list an "income type" that is different from the "income type" used by me & my recipients, so I use some additions to translate the original "income type" to my "income type". Take a look at this... (Note, the attached file will be easier to view).
| Income Type Description | Amount Earned | MyType | Mech | Partial Mech. | Sync | Full/Part |
| SYNC - STREAMING VIDEO | 0.01 | Sync | 0 | 0 | 0.01 | Full |
| SYNC - STREAMING VIDEO | 0.01 | Sync | 0 | 0 | 0.01 | Full |
| SYNC - STREAMING VIDEO | 0.01 | Sync | 0 | 0 | 0.01 | Full |
| STREAMING - Mechanical | 0.02 | Part Mech | 0 | 0.02 | 0.02 | Part |
| STREAMING - Mechanical | 0.03 | Part Mech | 0 | 0.03 | 0.03 | Part |
| STREAMING - Mechanical | 0.03 | Part Mech | 0 | 0.03 | 0.03 | Part |
| STREAMING - Mechanical | 0.03 | Mech | 0.03 | 0 | 0.03 | Full |
| STREAMING - Mechanical | 0.03 | Mech | 0.03 | 0 | 0.03 | Full |
| STREAMING - Mechanical | 0.03 | Mech | 0.03 | 0 | 0.03 | Full |
The "Income Type Description" is from the original DB, but I simplify it to "MyType". For this example, I move the "Amount Earned" into the appropriate column (in this example, "Mech", "Partial Mech", or "Sync". The formulae in the "Mech" and "Partial Mech" columns work, but the formulae in the "Sync" ALWAYS copies the "Amount Earned" regardless of it is appropriate. For example, look at the last row -- the "STREAMING Mechanical" gets looked up in a table (not shown here) and is found in the column evaluated in the VLOOKUP formulae in the "MyMech" column, but the VLOOKUP in the "Sync" column returns the last value in the "MySync" lookup table list. In short, the VLOOKUP in the Sync column always finds a value, even though it's not in the list.
So, why does it work for "Mech" and "Partial Mech" and not for "Sync"?
Thanks in advance.
