•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Tracking totals over all sheets, but from different cells
Our take
Managing project hours across multiple sheets can be challenging, especially when daily totals are located in different cells and rows. To automate your grand total, consider using a combination of functions like INDIRECT or OFFSET, which allows you to reference the specific cell containing the daily total, regardless of its position. This approach can streamline your tracking process, allowing you to efficiently sum the hours as new sheets are added without manual intervention.
Hi, I'm trying to keep an automated running total on working hours for my project. I have one sheet per day, each with a cell that sums the total hours for that day (reads 40 in my screenshot), dating back to October.
I've been tracking this manually with a rather bulky SUM function on a dedicated sheet, and it works just fine, but I'd like to try to automate the grand total to sum each day as it's created, rather than manually adding the cell each time I create a new sheet. The issue I'm running into is that the daily total doesn't land on the same cell every day; I am constantly adding & removing rows as people come & go from the project. There are also multiple cells that read total hours; one for the subcontractor sections, one for my own company's section, and the bottom one which sums the whole day - this is the one I'm trying to track.
Is there a way to filter for this cells contents on each sheet based on the Daily Total cell in the bottom right perhaps, even though they're not in the same row?
As an aside, I'm not allowed to change the layout to make this easier; this is a standard form at my company and the formatting needs to stay the same.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Monthly tracking workbook I use to track employee sales metrics; Trying to find a way to make the process less labour intensiveTruly having a hard to describing my issue effectively but hoping someone can help. First time posting here and I'm by no means an expert with excel, so please be kind! I have a monthly workbook where I track each employees revenue and other metrics. Every 2 weeks for payroll, I provide a print out of these numbers, and the payroll sheet pulls data from multiple sheets in the workbook. For example, every workbook has a separate sheet for each day of the month, titled "1" through "31". I have pay period sheets, so I'll use one titled "04.02.26 - 04.15.26". Then I'll have the data for each employee pulled from multiple sheets. For example, I use the formula =SUM('2:15'!E3) to pull the sales data from each day of that period for the specific employee. This works quite well. However, when I create a new month's spreadsheet, I have to manually alter this formula for each employee and for each data point (more than just revenue, at least 6 different data points for 7 employees). Is there a way to automate this? For example, a cell or two where I'm able to enter the date range and all of the formulas update to that date range for the corresponding pages? I'm sorry of this post is confusing. Truly it's confusing even typing it! submitted by /u/Ok_Smile9222 [link] [comments]
- Looking for project tracking ideasHi, As the title suggests I’m trying to create an Excel sheet that tracks the progress of various projects. Essentially, I was given a messy excel document to look after. I don’t have particularly advanced skills and nor do I want to spend too much time on this. So… One sheet lists projects as rows and they all have a reference code. There is a column designated for “action updates” where people overwrite progress each month. Other columns exist for project status, dates etc. My idea was to create another sheet which also lists the projects but acts as an action/change history log. I attempted to have a drop down through grouped cells that would act as a historic list of all changes and action updates around each project. The row with the corresponding project number would act as a “live view”, so using an X look up to display this data on the original sheet. Is there a better way to create something like this? There must be loads of ways to do something like this, but I just can’t think which way to do it!! Thank you for any help!!! submitted by /u/Formal_Disk_3760 [link] [comments]
- How to consolidate multiple sheets into one YTD summary report?I have 52 sheets in my excel workbook. It is named in a non-traditional sequence: "Jan Wk1, Jan Wk2, Jan Wk3,...... Dec Wk 4, Dec Wk5", because it's easy for me to print a weekly report with a total for each category. https://preview.redd.it/rq7qb3x8ddyg1.png?width=814&format=png&auto=webp&s=9d101ae17b185451e5140ef1c17f86221cd83aa6 Log 2026 is what my spreadsheet looks like. I only included three weeks as an example. Cell H34 in the highlighted area, on the Jan Wk2 sheet, contains a VBT to retrieve data from the previous sheet. Each day, I also need the total from the previous day in C36:G37. I like my current setup because I can easily convert it into a weekly PDF report without any modification. However, I'm not sure how to create a YTD summary report from 52 sheets. A template of the YTD report is included in Log2026. I tried to use 3D formulas, but I have to manually change sheet names and adjust cell references when crossing columns and rows. not very pragmatic and easily make mistakes. Log 2026: https://drive.google.com/file/d/1IEavZiJiDOv4GS-8c6n8dJqHUnRfT-ft/view?usp=drive_link ***************************** Some people suggest combining a year's worth of data into a single spreadsheet, rather than 52 weekly sheets, and then using Power Query to populate the report, but I am having difficulty generating the weekly report using the preferred template. Preferred Pdf report example: https://drive.google.com/file/d/1uQW7z_XZJ_Nn5sAzcaTkXDObAu8XTi0e/view?usp=drive_link Screenshot of combined data Completed data: https://docs.google.com/spreadsheets/d/1xIJkqTOwjucN2QMT0pK6A6wmSp8U4U8A/edit?usp=drive_link&ouid=102145557861966136866&rtpof=true&sd=true highlights: data collected daily, so every day is different. data for the drivers on the bottom of the spreadsheet changes based on their starting date. So my goal is to create a weekly and yearly report with preferred template (boss's request). Any suggestion is appreciated. submitted by /u/Ok_Solid_9312 [link] [comments]
- Help on tracking daysHello, I am pretty new to spreadsheets and I hit a wall and google is not helping. Signed up to reddit just to ask this. Not sure how to explain this easily so there will be examples of what’s going on. For the multiple projects I am tracking days taken using the formula the subtracts weekend and holidays for each phase Project name. Phase. StartDate EndDate DaysTaken ———————————————————————————————————— Project 1 Phase A 10/31/25 11/11/25 8 Project 1 Phase B 11/12/25 12/4/25 15 Project 2 Phase A 10/31/25 11/11/25 8 Project 2 Phase B 11/12/25 12/4/25 15 I am putting this into a pivot table and averaging days taken for each phase from all the projects Project name. PhaseA. Phase B ————————————————— Project 1 8 15 Project 2 8 15 ————————————————— Average 8 15 Now I have a project that phase had to pause and wait for something to finish. So the data looks like this Project name. Phase. StartDate EndDate DaysTaken ———————————————————————————————————— Project 3 Phase A 3/6/26 3/11/26 4 Project 3 Phase A 3/16/26 3/20/26. 5 So the pivot table is only taking the 4 not the 9 Project name. PhaseA. Phase B ————————————————— Project 1 8 15 Project 2 8 15 Project 3 4 ————————————————— Average 7 15 I know if I change the plot table Values to Sum it will count the days as 9 but it will just add all the phase days together at the bottom. Is there a way to get the 9 to show up, but still get the total average at the bottom of the column? submitted by /u/The_Purple_Orc [link] [comments]
Tagged with
#Excel alternatives for data analysis#natural language processing for spreadsheets#generative AI for data analysis#rows.com#financial modeling with spreadsheets#automated anomaly detection#Excel compatibility#row zero#cloud-based spreadsheet applications#real-time data collaboration#real-time collaboration#Excel alternatives#google sheets#automated running total#working hours#daily total#project management#SUM function#sheet per day#tracking totals