Automate date range entry

[mikecox39]

I'm the treasurer of my HOA. I created a budget spreadsheet that works well but I will be giving up the position a some point so I want to create a Dashboard for the person who takes over for me, to make it "user friendly".

Every year I have to start with a new sheet, changing the dates, removing data, etc. Since the fiscal year starts next month I decided to work on automating the process of setting up the new spread sheet.

see the attached link for details.


https://app.box.com/s/l9ejhyrxoamweuvf649dg9le58iu21zc

 

[mikecox39]

Thanks for the Wait code, I will add it to my list of favorites.

I plan tol try it but I doubt I will need it because it's easier to just run the macro on my HD then save a copy in both places. Once it's been updated I can work with the new file from either place.

I did get a response from my post about the cloud that wasn't very encouraging.

On a completely unrelated topic (I suppose I should create a new post) I just got this promo from the instructor of a course I'm taking. Do you know what this new XL for 365 is all about? I have Office 365 on my machine so I'm guessing it's applicable but is it something everyone should be learning? Will it really be better or "easier" and will it replace the current version sometime in the future?

https://courses.xelplus.com/p/excel-dynamic-array-formulas/?product_id=1212312&coupon_code=LAUNCH_DA

 

[NoSparks]

Running from the HD you could go back to about post 15 for a working macro, that other stuff was added as an attempt to track down what was going on.

Regarding your unrelated topic, no idea what that's about.
With the amount of activity there is on all the different Excel forums, I doubt there will be much change in the foreseeable future.*


* I have been wrong before.

 

[mikecox39]

Yes, I've saved all the iterations of the macro. Thanks

 

[mikecox39]

I'm sorry to be back but...

I've been making entries into my updated MB19 spreadsheet and the Data from the Details sheet isn't getting added to the Master sheet.

The formula for getting the amounts from the details page to add up and be added Month cell of the Category on the Master sheet is not working now. It is working on MB18, the sheet used to update MB19.

This is a very complicated formula that truly baffles me. I see a [date] in the code but I don't know how to interpret it. Could that be the place where things have gone wrong? Should this code have been updated as well, to make it relevant to the updated dates on the Sheets?

=SUMIFS(tbl_Details[[#All],[Amount]:[Amount]],tbl_Details[[#All],[Account]:[Account]],Master!$A30,tbl_Details[[#All],[Date]:[Date]],">="&Master!D$1,tbl_Details[[#All],[Date]:[Date]],"<="&EOMONTH(Master!D$1,0))

https://app.box.com/s/l9ejhyrxoamweuvf649dg9le58iu21zc

 

[NoSparks]

When you made the " Details orig" sheet, it really threw a wrench in the works.
tbl_Details hasn't moved, it still on the same sheet, just not the one you're now entering the details on.

Notice in the section of code that clears where you are now entering details, you had to change the table name ?

The simplest fix will be to make a copy of what is now "Details orig" and on the copy convert the table to a standard range.
Clear the table on "Details orig" ready for new details.
Delete what is now "Details" and rename "Details orig" to "Details"

Let me know if I should do it and post the workbook.

 

[NoSparks]

I did it.
https://app.box.com/s/kbbqmpsncn42tjjqhb7fq0xt7t1pak6g

 

[mikecox39]

When you made the " Details orig" sheet, it really threw a wrench in the works.
tbl_Details hasn't moved, it still on the same sheet, just not the one you're now entering the details on.

Before we go any further let me address this sheet.

I'm not at all sure why that sheet got added because there is no reason for it to be there!

I tried deleting it but it didn't help.

It's past my bedtime so in the morning I will try another run with the macro, and create another "update", making sure this sheet doesn't get on the update. I'll let you know what happens when it's done.

Opps, I just saw your second post. I'll take a look at it in the morning.

 

[mikecox39]

Ok, I couldn't wait

Thanks! It looks like you've fixed it.

Would it mess anything up if I just deleted the "Copy of 2018 Details" sheet, since it shouldn't even be in the workbook?

 

[NoSparks]

Deleting it that won't matter at all, it's just a copy of that ' Details orig' sheet with the table on the copy converted to a range.
The sheet was renamed to make sure it wouldn't be confused for anything else.
If you click the little drop down arrow on the formula bar on the left where the active cell address is,
you'll see you're back to only your original tables.


fyi
The way I discovered the table issue was to click into Master sheet C25, then
from the Formulas ribbon, Formula Auditing section, Evaluate Formula.
This is where you can see how Excel handles the formula each bit at a time and it showed the sheet of the table.

 

[mikecox39]

Formula Auditing section, Evaluate Formula.
This is where you can see how Excel handles the formula each bit at a time and it showed the sheet of the table.

Great! Thanks again and thanks for for showing me this.

Now I can study that formula, which was developed by someone like you, on this very forum! Maybe I will finally begin to understand how it works.

fyi, I am currently working through a very long course in VBA. I'm currently in section 6, dealing with Error Handling so this is timely.

This course has 20 sections, divided into 7-12 sub-sections with 15-20 minute running times but which take me at least 90 minutes to process, going step-by-step! I may die of old age before I finish it

Cheers,