Can I make a "mother row" to assign values from?


We are a music library company, and as such a large amount of our income comes in as small royalty payments that add up incrementally. The information comes from 15+ different collection sources, and in general the only thing consistent between them all is the track title. That means that the information from the royalty reports is missing data such as composer information, key words, instrumentation, genre, etc. However, when we send out the albums for publication we fill out a huge metadata spreadsheet that has all this information.

What I'd like to do is somehow use the metadata spreadsheet to assign values to the royalty sheets. For example, if the "metadata track title" = "royalty track title", then I assign the values from the metadata rows to the spreadsheets. Is this possible? I've tried a handful of different things with the formula tile and other things, but what I keep running into is that one row can't be compared against another row; it's always only comparing columns against columns. Right now, my only work around is to manually enter the information for every single track:

[name created from new formula column is `Composer`]


WHEN `Track Title` = '[Track Name]' then '[Composer]'

else null end

I'd have to do it like this for every single bit of information I'd like to add, such as: Composer 2, Composer 3, Composer 4, Composer 5, sometimes even Composer 6, key words, mood, genre, uses, instrumentation, and more.

Obviously, that is a huge pain and after dozens and dozens of hours are spent catching up the rest of the catalogue, it will still require that I update the information every single time we release a new album. So is there a way that I can just pull the information from the metadata sheet? The information is already there, I just wish I could figure out how to pull and place it where it needs to go. Thank you!

Best Answer