I'm looking to use Data Mesh to automatically import data from a source sheet into a target sheet. My initial approach was to use index/match but I quickly hit the cell link limit of 100,000. In a nutshell I have an employee list with roughly 20k lines each containing standard information like name, email address, dob, etc. I've created a separate grid with a form attached that would collect name, email, and some other information; we're using the setup to allow managers to collect workers compensation information at worksites.
I set up a data mesh configuration between the massive source sheet and the sheet that will collect workers comp claim information. I have the email address as the key in the data mesh configuration and it's working... sort of.
Once the name and email is collected from the form and populates on the spreadsheet, I would like Data Mesh to automatically run and populate additional information like dob, address, etc. I have to manually run the Data Mesh to have the information populate as (I didn't realize this when I set it up) the workflow is looking for a change to the source sheet.
I'm not sure if there's an approach that would have the data automatically update. My concern with vlookup is the maintenance as the sheet grows.
Any suggestions are welcome.