I am currently working on a sales sheet for my team and have a question.
I currently have each month separated into different sheets for ease of use with our current workflow and would like to be able to pull averages for each employee but would prefer it only be averages for the most recent month. Is there a way I can read either the most recently created sheet or with the latest month name and feed that sheet name into a formula?
A few of the ideas I had were possibly using a script found here to create an array listing all the sheets and using an INDEX-MATCH in order to find the latest month and input in into a formula but I'm having issues getting the script to update when a new sheet is made.
Another idea I had was just to use a FILTER or a QUERY to aggregate every transaction into one table on another sheet and pull my averages from that sheet instead but I'd prefer not to use a calculation table if I can avoid as it will get pretty messy with all the employees added. Any help of suggestions would be much appreciated.