Rolling budget/spend tracker

I have a bit of quandary with a recurring task, wherein I am tracking weekly budget allocations and tracking what is NOT spent.

Currently, I have a basic sheet where my contractors/vendors upload a patient record, which is just a sign-in sheet, and I manually count the hours used. Each of these sheets represents a maximum of 5 hours that I can allocate. I have 7 locations each week to track. This is dead simple, takes no time at all, and I don't mind the manual entry from the uploaded attachment.

What I need to do is to to track the money NOT spent. So if a location only utilizes 3 hours out of their allotted 5, I want to track this and add it up each month, so that I know what "extra" is available to spend in subsequent months.

I also have a separate sheet that is just parent rows for each of these locations, and child rows for each of the dates for the year in which they have something scheduled. I don't have access to Data Mesh or Data Shuttle, so I'm not sure there's a way to link these two sheets. Is copying and pasting just as easy? I'd like it to be automatic of course.

I also do not have a dashboard for this spending, which I probably should.

Thanks in advance for input!

Find more posts tagged with

Sheets

Formulas

functionality

Automations

Cell linking

Accepted answers

All comments

Paul Newcome

Are you able to provide some screenshots for context (mock data is fine)?

jmaxbr

Still working on this, sorry for the delay. I have to redact a bunch of the info as it's "corporate" non-public. I think in this case the easiest is just to have two sheets open. In an ideal world it would be automatic, or my sheets might be built differently, but I'm trying to keep them simple as well.

Paul Newcome

If it is easier, are you able to use mock data instead? Just make copies of the two sheets, remove all live data from the copies, and then use fake names and whatnot for a few rows just so we can see the structure and the intended result?

jmaxbr

Well, my solution ended up being rather inelegant, dropping this formula into my parent rows (for each location), and I'm going to just change the dates each month:

=(COUNTIFS(CHILDREN(Date@row ), >=DATE(2026, 1, 1), CHILDREN(Date@row ), <=DATE(2026, 1, 31)) * 5) - SUMIFS(CHILDREN([Hours Used]@row ), CHILDREN(Date@row ), >=DATE(2026, 1, 1), CHILDREN(Date@row ), <=DATE(2026, 1, 31))

So this counts the number of occurrences in each month, at each location, and then subtracts it from a baseline of 5 hrs per month.

Not ideal, as I really should be tracking this over/under situation for each month, probably on a separate sheet. I have some conceptualizing to do!

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.