Here is what I am doing. I run weekly activity reports of a sales team and graph the corresponding results. Currently, I manually edit the graphs and choose which weeks to show on the graph, but I want to automate the graphing by always using the latest 12 weeks of data. I plan to do this by using an INDEX MATCH formula.
I have one sheet for all of 2021 with each column having a date, such as the Monday of every week (5/3, 5/10, 5/17, etc...). This sheet is where I place all of the RAW data for every week of the year. On a separate sheet, I have a table that will be auto-populated with the last 12 weeks of data based on whenever the file is opened because it will compare the TODAY () date against the sheet with the columns of raw data.
I need the table, that I will be using to graph, to auto-populate with whatever the last 12 weeks are. My plan was to place a "1", "2", "3", etc... in 12 corresponding columns. Based on the TODAY () date, the sheet will look to the raw data DATE sheet for each week and then assign the "1", "2", "3", etc... to those raw data columns. I will then use the INDEX MATCH to pull the 12 weeks of data into my graphing table.