How to optimise my currentTimesheet solution

Hi community,

hope you are all spending a great xmas period.

I'm here to ask you advice on how i could improve or change completely the architecture of my timesheet solution.

I have setup up a sheet with the following main columns: Work date - Contact - Project Name - Activities

A total of 15 users enter their activity using a simple form linked to this sheet. One activity per project per day. Each user may then enter several lines for the same day.

Hours spent for each activities by each person on each day are then pulled all together in each project plan (Index Match with Project Name and Activity). Metrics are built from there.

The problem i'm facing is that since we put this sheet in place in May 2025 i have more than 3500 lines and several millons of Cross sheet formula (limit of 25M is close).

Every project (currently +/-15 ongoing) last more than 2 years and i'm quite sure that my actual sheet will not have enough sapce to contain all information and all the cross sheet formulas. Additionally there might be some project ending while other just starting in the same year.

So, i'm wondering what could be the best way to allow the team to note their hours and pull them up all in eaech project plan at the WP level.

I was thinking about RM but unless i'm wrong the reporting can be done only at Project level.

I was thinking to data table: having a timesheet every 6 months, pull all togheter in data table and then reference the data table in my Project plan.

Another thought was to move to table view (bigger data size) and sue an automation to archive lines older than 3 months, then adapt the Project plan formula to point at this archive sheet. By default the archive sheet will not carry on the cross sheet formula if i'm correct.

N.B. number of users may increase in 2026.

I hope i was clear and i look forward to any advice :)

Thanks in advance for all your feedbacks

Davide

Find more posts tagged with

functionality

Project Management

Accepted answers

All comments

kylewhatt

Hi Davide,

You’re right to question the current setup. The main problem you’re hitting is not the number of rows, but the sheer volume of cross-sheet formulas. When you rely heavily on INDEX and MATCH across multiple project plans, the system reaches the 25 million cell reference limit very quickly. With projects lasting more than two years, that pressure only increases over time.

In situations like this, the key improvement is to stop doing heavy calculations inside each project plan. Project plans work much better when they only consume already summarized data instead of pulling and calculating raw timesheet rows.

Your idea of using a Data Table is actually the strongest long-term option. Data Tables are designed for large transactional datasets like timesheets and can hold far more rows without performance issues. A common approach is to keep a simple input timesheet for users, then use automation to push those rows into a central Data Table. From there, you can build reports or summary calculations and let project plans reference only high-level totals instead of thousands of individual entries. This alone reduces cross-sheet formula usage dramatically.

Reports are also very useful here. Many of the metrics you’re currently calculating with cross-sheet formulas can be handled through reports filtered by project, activity, date, or user. Reports do not count toward cross-sheet formula limits and are much easier to scale as data grows.

Archiving older rows can help with performance, but only if your project plans are no longer referencing that archived data. If cross-sheet formulas still point to archived sheets, they will continue to count toward the limit. So archiving is helpful, but it should be part of a larger redesign rather than the only fix.

You’re also correct about Resource Management. It works well for high-level project tracking, but it’s not ideal for detailed work package level timesheet history like the one you described.

In practice, the most scalable setup is a simple user input timesheet, automated into a Data Table, with reports and summaries feeding dashboards and project plans. That structure will continue to work even if the number of users increases and projects overlap year after year.

You’re definitely thinking in the right direction, and with a small architectural shift, this solution can stay stable for the long term.

David3_DigitalFlows

Hi @kylewhatt,

thanks for your comments wich are very helpfull.

Just one question to better undrstand:

"A common approach is to keep a simple input timesheet for users, then use automation to push those rows into a central Data Table. From there, you can build reports or summary calculations and let project plans reference only high-level totals instead of thousands of individual entries. This alone reduces cross-sheet formula usage dramatically."

So practically, if i get it correctly you suggest to setup a timesheet entry sheet wich keeps only a limited number of entries (let's assume 1 week), fed thought a simple form for end users. A offload data shuttle takes these entries into a CSV and an Upload workflow is feeding the Data Table.

I'm a bit lost when you suggest about reports etc. How would you build reports or summary calculations from the Data Table ?

Thanks

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.