Monthly spreadsheet- need to update summary sheet based on date

**hootiebsc** · 03-04-2013, 07:48 PM

Ok. Here's my issue.

I have a spreadsheet with 12 monthly tabs and a summary page with current day, month to date, and year to date.

The issue that I am having is the month to date.

I need to pull the month to date totals from each spreadsheet based on the current month.

For example, I would need to pull march's MTD this month.

On April 1st, I would like to pull that MTD

The catch is...i would like for it to be done automatically.

VBA is not my strongsuit. Would a "sumifs" work in this case?

Any help would be great.

-Adrian

**dredwolf** · 03-04-2013, 08:29 PM

depending on how your workbook is set up, this may be simple, or very difficult, simple = the tabs for each month named to play nice with formulas and the Current totals of each month in the same cell on each sheet---> Extremely difficult = oddball sheet names, Current totals located anywhere,etc...

A sample workbook set -up similar to what you have would help tremendously

**hootiebsc** · 03-05-2013, 12:42 AM

I have attached a sample.

The tabs are basic three letter months and the worksheets will all be the same. No changes. I will make every month have 31 days and just black out the dates that do not apply.

Nothing wild and crazy.

**dredwolf** · 03-05-2013, 01:36 AM

I am going to assume this example is your Financial year, so I don't have to worry year crossover issues ( meaning that the summary only includes the financial year...which I'M assuming is the workbook

)...if my assumptions are wrong please let me know

**dredwolf** · 03-05-2013, 03:39 AM

Also this would be in 'Extremely hard' range..16th of each month is missing, you got the data split in half over 14 columns..would you mind if I reorganized it a bit?

EDIT -
this may take me a bit to deal with, getting late here

**dredwolf** · 03-05-2013, 03:52 AM

Also..A re-confirm...the sheets in workbook are the calcs for year to date? or just the ones in 2013 ?

**hootiebsc** · 03-05-2013, 10:09 AM

Yes. This is YTD Oct 2012 - Sep 2013

Absolutely, please reorganize it as you see fit.

Thank you so much for your help...

**dredwolf** · 03-05-2013, 02:23 PM

See if the Attached will work for you
I added a Named Range called Months (check in the Name Manager) to ease the lookups and reorganized the layout of the monthly sheets

Hope this helps

**hootiebsc** · 03-05-2013, 11:04 PM

That's awesome! I appreciate your help.

One more question...and i hope I am not being too much of a bother...

With the indirect function for current day... is there a small change that can make it previous day?

Once again, thank you so much for your help.

**dredwolf** · 03-06-2013, 11:28 AM

Try this :TRR ProtoSol2.xlsm

Hope this helps

**dredwolf** · 03-06-2013, 11:59 AM

Looks like the gremlins are back..not sure if last post showed up or not

**hootiebsc** · 03-06-2013, 03:16 PM

That is great. I should have figured that was the solution.

When I modify the table for aesthetics and I either add or remove columns or rows how do I adjust the formula in the summary tab in order to preserve the functionality?

**dredwolf** · 03-06-2013, 05:14 PM

Hrrrm...going to take a bit of figuring for this, got to find the table on the page, and the Name within the table...might be awhile figuring out all the offset/indirect/match's for this..

Give me a bit, and I'll come up with something...the formulas are probably going to be quite long, just a heads up

**hootiebsc** · 03-06-2013, 05:53 PM

LOL! I didn't think that the formulas could be longer than what they are...

Thanks dred...

**dredwolf** · 03-07-2013, 10:29 PM

Try this :TRR ProtoSol3.xlsm

I have left notes on the 'Summary' sheet and the 'Oct' monthly sheet to help out

IF you want the basic structure of the tables to change, then the formulas will have to change as well

Hope this helps

EDIT-
Also note--you sometimes will have to hit 'F9" for the summary sheet to update after editing a month sheet..

EDIT 2 -
Now you should understand my first post:

depending on how your workbook is set up, this may be simple, or very difficult, simple = the tabs for each month named to play nice with formulas and the Current totals of each month in the same cell on each sheet---> Extremely difficult = oddball sheet names, Current totals located anywhere,etc...

**dredwolf** · 03-08-2013, 02:17 PM

Revision

Noticed some circular references popping up that were not there when I was developing the solution (

), so I got rid of them, and some minor tweaks to a few formulas
(getting rid of the circular references seems to have greatly reduced the size of the file...lol)

Monthly spreadsheet- need to update summary sheet based on date

LinkBack

Thread Tools

Rate This Thread

Display

Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Re: Monthly spreadsheet- need to update summary sheet based on date

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions