-1

I am currently working on a sales sheet for my team and have a question.

enter image description here

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.

Improve this question
0

From the question

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?

The easier way to do this is by using Google Apps Script, perhaps by using a custom function.

You could use developers meta-data to save the date that each sheet was created but it will be a way more easy if you use the sheet name rather than this alternative.

Having a list of the sheets is an an option but you should somehow update that list. To do this automatically you could use a on change installable trigger.

References

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.