Forum Discussion

zclem's avatar
zclem
Copper Contributor
Jan 06, 2025

How Can I Make My Life Easier Here?

I have created an excel file where I am collecting data across several different tabs. I am tracking weekly data and monthly data for two different data sets. Some of it is manually entered and then everything else is set to automatically fill in from that manually input data. I have a weekly data tab that I have to copy and paste a table I created for each week and then I have to manually go through and change the cell references and same for the monthly tabs. It takes absolutely FOREVER to do this, and I am just wondering if there is something that I can do to make this process easier. 

Here is the data table:

So, the formula for C499 is:

=COUNTIFS(Audits!$B:$B,[@[Jr Underwriter]],Audits!$H:$H,"Fail",Audits!$D:$D,">="&$C$497,Audits!$D:$D,"<="&$E$497)

It takes data from another tab and searches for the word Fail through the dates listed in cells C497 and E497. When I go to create the table for the next week I copy and paste this table, change the dates in those for the next week and then I have to go through and change the cell reference (in red) to match the new cells in the new table. So, I want the formula to follow the table (but the cells are not part of that table which is the problem) and not the cell if that makes any sense? I tried to create a second header row for the table so it had its own cell name but it's impossible from what I can see, or I am just not finding it. I don't even know if this is possible to do but if it is it would make my life SOOOO much easier. 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    If you really want to make your life easier you should stop creating weekly/monthly tables and use some of Excel's built-in features to analyse data from the Audits sheet, such as pivot tables, power pivot, power query. Though, you haven't said anything about the structure of the data in the Audits sheet. Add the ISO-week number to each record (as it seems your weeks are Mondays to Fridays) could be helpful as well.

    • m_tarler's avatar
      m_tarler
      Bronze Contributor

      I agree with Riny to not create new 'reports' every time and to use excel built in features like pivot tables, power pivot, slicers, and filters to create the report as needed for that time frame.  But if you insist on copying and pasting a new table for each, then why not just add a column to the table for Start Date (and End Date can be calculated, I assume). so then it is [@StartDate] or if you don't want to fill the whole column: MAX([@StartDate]).  Or you could base it on how many tables you have on the sheet using COUNTIF($A$1:$A499,"Week Of:") and based on that calculate which week it is and then the start date.  You could combine the 2 so a [StartDate] column uses that formula to determine the StartDate and then other columns use that column for their filters.

  • Take this:

     

    1. Use Named Ranges: Instead of using cell references, you can create named ranges for your date cells (e.g., StartDate and EndDate). This way, you only need to update the named ranges each week, and your formulas will automatically use the new dates.
    2. Dynamic Named Ranges: You can create dynamic named ranges that automatically adjust based on the data. For example, you can use the OFFSET function to create a range that expands as new data is added.
    3. Structured References: If your data is in an Excel Table, you can use structured references. These references automatically adjust as you add or remove data. For example, instead of Audits!$B:$B, you can use Audits[ColumnName].
    4. VBA Macros: If you're comfortable with VBA, you can write a macro to automate the process of copying the table and updating the cell references. This can save you a lot of time and effort.
    5. Formulas with INDIRECT: You can use the INDIRECT function to create dynamic references. For example, you can build a reference string that changes based on the week number.

Resources