My team updates documents (docs). We have two sheets that we call trackers:
- Document Tracker: Our main sheet tracks which documents are being updated. In one column we have the document title and in another column we list any record #s that are driving the updates to that document.
- Record Tracker: On another sheet we track the individual records associated with updates. Each record has a 6-digit ID (For example: 111111, 123456, 989898, etc.).
We want to automate the Record Tracker to make new record entries as they are added to the Document Tracker, but the problem arises when individual documents having multiple records and also records having multiple documents. I cannot use dropdowns since there are too many records and documents.
Situations:
- Some docs will have no associated records. These items do not need to be in the Record Tracker.
- Some docs will have only 1 associated record. These are easy to work with.
- Some docs have multiple associated records, but there is no max number of records that can be associated to a document.
Here is a made up example of the two sheets:
- I can setup a Data Mesh to add new entries to the Record Tracker, but it sees entries with multiple Record #s as their own entries and not individual records.
- I can also set up a formula on the Record Tracker to combine all document titles from the document tracker that are associated to a given record #, but it will only populate documents that are being updated for the one record, not items that have multiple record #s.
Is there a way to automate separating the Record #s from the Document Tracker as individual entries in the Record Tracker with their associated document titles?
