I have written similarly awful formulae when I needed to get a csv to export to Google Calendars, but the program I was exporting to wasn’t using Google’s accepted csv headers.
I ended up creating a template that looked at the exported csv file, and then reformatted it in a way that Google liked (and added some extra info along the way.) I needed it to only fill text if an entry actually had info in it, and hide all the text otherwise. So that I could automatically delete empty cells and avoid a bunch of empty calendar entries when importing it into Google. The resulting formula for some of the fields was… Not great. This is what controlled the “name” of each calendar event:
It takes several different potential fields, and combines them into a single field. If there are no entries, it gets left blank.
And every single time I would get it working properly, someone would add a row or change the data validation rules, so I would have to go in and update my formulae. After the fifth or sixth time that happened, I told the person making the changes that it was his job to update the formulae. Suddenly, it stopped getting changed.
Damn. At that point I would just switch over to a macro. Although I also have some formulas that xlookup a value, concatenate it, and the xlookup the new concatenated value.
I have written similarly awful formulae when I needed to get a csv to export to Google Calendars, but the program I was exporting to wasn’t using Google’s accepted csv headers.
I ended up creating a template that looked at the exported csv file, and then reformatted it in a way that Google liked (and added some extra info along the way.) I needed it to only fill text if an entry actually had info in it, and hide all the text otherwise. So that I could automatically delete empty cells and avoid a bunch of empty calendar entries when importing it into Google. The resulting formula for some of the fields was… Not great. This is what controlled the “name” of each calendar event:
It takes several different potential fields, and combines them into a single field. If there are no entries, it gets left blank.
And every single time I would get it working properly, someone would add a row or change the data validation rules, so I would have to go in and update my formulae. After the fifth or sixth time that happened, I told the person making the changes that it was his job to update the formulae. Suddenly, it stopped getting changed.
Damn. At that point I would just switch over to a macro. Although I also have some formulas that xlookup a value, concatenate it, and the xlookup the new concatenated value.
That’s bonkers but I can follow it, I think. Good work!