Creating a updating master list from multiple tables

Hello everyone!

So here is the deal: the wife and I are going to England for 18 days. For reasons that I don’t even know, I am creating a Excel spreadsheet that we will use to plan each day. The spreadsheet is attached. Basically there are many different worksheets for each of the days. For each day, there is a column for the day (to be hidden later), activity, cost, and comments.

Here is my desired result: On a separate “Costs” worksheet, I want there to be a master table that updates each time a new activity for that day is added in their respective tables. An added bonus would be that this master lists for “Costs” would not include items that have a cost of $0. (Of note, the data for the individual days can either be in a table or a range; it doesn’t matter to me. However, I would like the master to be a table for sorting purposes)

The spreadsheet I have created so far is attached.

At this point, I have tried multiple ideas (consolidation, pivot tables, msquery) and I am pretty much at my wit’s end. I figured at this point it really isn’t that big of a deal to have but I would like to have it in there if I can. As such, I am reaching out to humans that can give help for my specific situation.

I haven’t got in to the realm of macros with this so far, so I hope this is in the right subforum. If it isn’t and there is a better solution, feel free to move.

Thanks in advance!

That is a valid point. Yeah, I am fine with using an update button to run a script.

As for the master table, I want it to look just like all the other tables that it would be pulling data from. I have reuploaded the file.

You're right. I goofed on getting that table you requested earlier on the right sheet. Fixing now and creating and naming tables. Stand by.

Updated version is attached. I have created and named the tables, cleared out existing named ranges, and moved the example table to the appropriate sheet. Hopefully that will work better for you. Let me know if there is anythink else I need to fix, and thanks a ton in advance. I really appreciate the help!

That is perfectly OK. Probably best that I lay off the Excel for the night. I look forward to your help tomorrow.

Skywriter, thanks a ton for the help! That worked! If could rep you more than once, I totally would. I do have a question, though. I noticed that when you run the script, it is including in the master table items that have 0 cost. Is there a modification to the script that can keep those from appearing? I looks like (and I am guessing here...) there is a provision for just that in script you posted, but it doesn't seem to be working.

Thanks again in advance!

Skywriter, your pre-clicking is greatly appreciated! That did the trick. Have a wonderful night, sir or ma'am, and thank a ton for your help. I have been restrained from pulling my hair out.

Skywriter, On the result table on the "Costs" table, I can't get the formatting to stick with a pound currency symbol. Everytime I run the script it clears. What might I be missing here?

Skywriter, seems that a bug is rearing its ugly head here. In the attached version, you will see that I have done some formatting changes to the last version to put the final touches on it. The bug that I am running in to now is that, in its current form, when I update a cost in tblDay1 and run the report, the row on that report for the activity from day 2 disappears. Press it again and the button disappears and the data from the two tables on the cost page disappears. I have tried it with updating costs on other tables/days and other ways to get the bug to replicate, but, for now at least, it seems to just be for when I am updating a cost of tblDay2.

Any ideas? Thanks again for the help!

Skywriter, do you think you'd be able to help me with this? Any you can give would be greatly appreciated.