Help with dashboard creation

Hello Tiller community!

I’m updating the same thing in two different places which is the issue I’m trying to solve for. I would like to add an item in one place and have it update another sheet and a table in my dashboard.

To add a little more context. I created a dashboard and there are two tables I’m trying to update: my Bills for the next 3 months and a list of my active subscriptions. In order to do this, I created an “expenses” sheet which lists out all of my bills and subscriptions. Pictures of both the dashboard tables and the “expenses” worksheet are below. I have a macro that runs through the worksheet, updates the dates based on frequency, and updates the tables in the dashboard. Anything with “subscription” as a category also gets placed in the active subscription table.

The issue is that the information in the “expenses” worksheet is disconnected from data in the “Categories” and “Budget Plan” worksheets. Basically when I add, remove or update a bill or subscription, I also have to do the same in my expenses worksheet. I would love for the data in my expenses worksheet to populate based on the data in the categories or budget plan worksheets.

I am very much a novice when it comes to excel and am unfamiliar with most formulas, functions and error handling. The only reason my macros are functioning properly is because AI did most of the heavy lifting.

If anyone has advice on how to get my expenses worksheet to populate based on the categories and/or budget plan worksheet, it would be much appreciated. Or even if you have an idea that simplifies all of it. Maybe I don’t need an expenses worksheet and could write a macro to pull in data directly from the other worksheets. Maybe I don’t need a macro and there is a way to populate that data without it.

Just looking for advice, ideas, a solution or just to check my thinking. If there is a YouTube video that explains a formula or function you think I could use to solve this, I will definitely accept that as well.

Thank you!

Shay,

I always appreciate a fellow human that is on top of their budgeting and expenses. I’m still a novice to Tiller as well, but I really like it so far. I’m trying to determine if you are over complicating things or you just need to train your brain to view your items through the lens of what Tiller provides. I also have a separate tab in a spreadsheet that lists all of my active subscriptions. They really don’t change that frequently, so I update it manually. I do take those amounts and update budget in the Categories tab for the month that the subscription will hit. This automatically updates the Monthly Budget tab and the Yearly Budget tab provided by Tiller. This is probably not the automated solution that you are looking for but I just wanted to share my perspective on how I manage this similar situation.

Thanks,
Brad

1 Like

Hello Brad,

Ahhh yes, I do love to over complicate things. I do want things to be more automated. I travel quite a bit, so my bills and subscriptions change often. When I’m outside the US I’ll turn things like insurance etc off. Companies are always raising the prices of services, so prices need to be updated. Most services are subscription based so I’m frequently adding or removing them. For me, being able to update in one place and having those changes reflected in the Categories/Budget plan sheet and my dashboard would be really nice and make me very happy. And I think I’m getting close!

Right now, I’m just trying to bring in the correct bills and subscriptions to my expenses sheet. I have added a flag field to the Budget Plan worksheet. It has an xlookup function to check my expenses sheet for any category I’ve identified as a bill or subscription. The flag field will indicate the match has been found. Then I use the filter function in the expenses sheet to bring in any item with the marked flag field. That’s where I’m at and it’s currently working.

Next thing I’m trying to figure out is how to dynamically update the due dates of each bill and subscription. I’m sure there is a way use the start date of the bill, the frequency, and the frequency multiplier to check the current date and update the future due date based on those values but I’m still trying to figure that out.

Then to populate the dashboard with Bills for the next three months and Active Subscription, I think just the filter function will work. I think using a macro is overkill but seemed to be the easiest solution when using AI.

Anyways, I’m figuring it out as I go so It’s a slow process of trial and lots of error.

Thanks for your reply and I’m open to check my thinking or any critique.

1 Like

Hi, Shay,
Great planning ideas. You’re way ahead of me there, but the way I would do this would to put a Frequency column on the Transactions sheet. This should effectively synchronize it with the Categories and Budget Plan. I’m not as familiar with the Excel process as I am with Google Sheets. In Gsheets, I would use an array formula to lookup the payee description on your expenses sheet and return the frequency. Since this frequency data is now on the same sheet with other transaction and category information, it is simpler to make a pivot table or query appear on your dashboard. Using the number of months [1, 2, 4, 12, 24] rather than ‘monthly’, 'bimonthly", quarterly", etc may make looking back three months easier. In Gsheets, the formula for column F1 would be “=arrayformula(if(row(F1:F)=1,“Frequency”,iferror(vlookup(C1:C,{Expenses!$A$1:$A,Expenses!$B$1:$B},2,FALSE))))” I know this can be done in Excel but I don’t have the exact format at the moment. I hope I can look it up for you later this week, time and ADHD permitting.
Transactions Sheet:


Expenses Sheet:
image

best luck,
Chas

1 Like

Hi Chas,

Thanks for the insight! You are right, I did need to update how I was inputting my frequency and convert it to number of months. Calculations went a lot smoother after that. I also did update my columns to bring in more columns from the Budget plan sheet, namely Frequency and Mult. After that I went a little rogue but was able to accomplish updating the due dates of bills dynamically and get my dashboard updated with bills for the next 3 months as well as my active subscriptions.

I updated my Expenses sheet as shown below:

Once I brought in frequency and Mult from the budget plan sheet, I added another frequency column with an IF statement to convert the text of the frequency to the numerical representation of the months. Next i needed to calculate due date based off the start date, frequency # of months, multiplier and today’s date. I was able to do that with this function:

=IF(TODAY() > EDATE(A2, E2G2), EDATE(A2, (E2G2)ROUNDUP((TODAY()-A2)/(30.44E2G2),0)), EDATE(A2, E2G2))

Then I added a column “due 3 months” to flag due dates within the next 90 days with this formula:

=IFS(TODAY()>B2,“-”,DATEDIF(TODAY(),B2,“d”)<=90,“Y”,TRUE,“-”)

Next I went to my dashboard and added a sort and filter function combo using the flag for bills due in the next 3 months. For active subscription i just filtered by any with subscription as a category and that seemed to do it.

Now everything is linked up to the budget plan worksheet and automatically updating!

Still open to other ways of doing it or ideas to simplify. For now it’s working and I’m happy.

Thanks for the help and guidance,
Shay

Good work. Some of my rogue [i prefer to say creative] solutions have been quite complex, and months later I found a simple, elegant solution. But who cares? If it works, don’t fix it.

1 Like

I’ll take that advice! Spare your ADHD for something else :slightly_smiling_face:

:wave:, @henderson.shay

Did any of these suggestions help? If so, please mark one as the solution.

1 Like