EDIT: figured out how to actually import entire tables from other workbooks and have those tables refresh when I open the master tracker. I will just stick those tables in a hidden sheet and then reference those tables as I would any other table

Hey guys, I'm a bit stumped here because I've tried diving into Power Query myself and learning how to do what I need, but it's a bit over my head and I need to figure out how to do this within a pretty tight deadline. I'm hoping someone here can point me in the right direction:

A folder "test" has 5 different files, all called "Tracker - [firstname]", with different first names. The actual table and tab structure of each tracker is identical, but the values are not - one person's tracker might have 500 entries and corresponding values in their table, but another person's tracker might only have 5.

Let's take the table called "NewRelationships_TrackingTable". The first column [Column1] is the trackable entry, the second one [column2] is the date, and the third column [Column3] is the "amount" tracked. What I want to do is SUM everyone's amounts individually in a "master" tracker file, that's going to be saved in the same folder "test" with all the other files.

If I simply try to SUM between workbooks, the SUM breaks into #REF as soon as the trackers aren't open anymore. I understand there may be a way to use something called "power query" to do what I need, but even with ChatGPT walking me through it and two or three video tutorials, I'm still basically completely lost and going around in circles with ChatGPT hallucinating different buttons and instructions that don't exist.

Does anyone have an idea on how I could proceed?