I have created a document template to help me automate breaking down scripts for my job as an Art Director for Film/TV. I've made good progress with automating the colour coding needed and auto-populating fields based on the values of others. I did this using arrayformula and xlookup (See formula in BREAKDOWN!A2:A and BREAKDOWN!B2:B). The ultimate goal is to make the document sortable by columns, so I can sort it by Shooting Day, Scene Number, or Department, etc.
However, whenever I sort the document (Usually using Advanced sorting range prioritizing "START DATE" then "SCENE NUMBER" then "DEPARTMENT") the formulas in Columns A & B disapear.
Here is a link to the public access editable copy if anyone may have some ideas on how to fix my issue:
Thanks so much in advance!
You can put your arrayformulas in the headers to ensure that the formulas are not moved in the sorting. You can use VSTACK with the header's name and the formula. For example, in A1:
=VSTACK("START DATE",arrayformula(xlookup(C2:C,SETCODES!B:B,SETCODES!D:D,)))