Creating Monthly Data Table from Weekly Data Table

**nhojflies** · 06-01-2009, 05:39 AM

I have a table of data which represents data in different categories by week.

My ultimate goal is to have another table representing the data for each month - for instance - for each of the categories the data for :

06/04/2009
13/04/2009
20/04/2009
27/04/2009

is summed to make the data for the month of April.

The way I'm doing this at the moment is very long-winded

I'm using a whole new table - the size of the original weekly one - for each month. A calculation decides whether to effectively leave a cell blank or insert the appropriate data based on a date being within a particular range.

So in each "month table" there is the same list of week values:

06/04/2009
13/04/2009
20/04/2009
27/04/2009
04/05/2009
11/05/2009
etc.

but for each "month table" only the cells adjacent to the dates within the relevant month will return actual numerical values within them

This is an example of the forumula I am using in these tables:
=IF(AND(($AD7>=MIN($AE$5,$AE$6)),$AD7<=MAX($AE$5,$AE$6)),$C7,"NA()")

Then a master table sums the totals for each month.

I want to be able to keep this table but get rid of the ones for every month as the sheet is getting unwieldy!
I have tried several times to attach the sheet for clarity but each time upon trying to "Submit New Thread " I am getting page not found errors - the sheet is only 133KB and I have tried zipping and sending also - I can't make it any smaller.

**DonkeyOte** · 06-01-2009, 06:31 AM

I must confess I'm finding it difficult to follow what it is you're trying to do ...

If you merely want to populate the table in AE30 etc such that it gives you the data as listed in B5 etc but in a monthly format based on week date then perhaps:

Please Login or Register  to view this content.

this can be applied across the entire matrix (AG30:AT46)

**nhojflies** · 06-01-2009, 07:07 AM

that works great - thanks!

just one thing - as with other tables - for charting reasons I don't want to return a "0" if the month has no data in it - I've made it print an "NA()" which is conditionally formatted to be white and therefore "invisible" in the original table. Your formula is a bit beyond me - could you show me how apply the same rule to this formula?

Also I will probably want to ultimately have the "month" on a different sheet to the week one - what would be the easiest way to implement this

Thanks

**DonkeyOte** · 06-01-2009, 07:17 AM

Regards returning NA() so as not to plot:

Please Login or Register  to view this content.

Regards moving the monthly table to another sheet from the weekly data - all you need do is adjust the ranges highlighted in red such that they reference the weekly data sheet name (ie 'Stream vs Resolver'!$B$5:$B$26) ... adjust those in blue such that they reference the cells containing the data on the new sheet (ie the cell holding the date, the cell holding the header)

**nhojflies** · 06-01-2009, 08:53 AM

Works a treat

Thank you very much indeed

**nhojflies** · 06-01-2009, 09:27 AM

Works a treat

Thank you very much indeed

Just before I implement in full for various other sheets - I have attached the workbook again with two example sheets which show where the raw data is actually inputted - and is in reality the source for the charting and tabling sheets. (In the real workbook there are obviously many more such sheets)
You can see on these sheets how I've divided the days up, but the sheets themselves are divided into weeks.

The reason I'm showing this is because ideally I wanted to have the monthly data sheets source the whole set of weekly sheets (ie ones beginning with "WC - ") and sum the data for only the exact days which fall in each month - however in an early thread this was never replied to it - is it actually possible to do this with your formulas?

I should explain more about "summing the data" - it is effectively counting the instances of a particular word in a particular column...

**nhojflies** · 06-01-2009, 09:31 AM

oops - I forgot to attach the new sheet - here it is - sorry some of the calculations have botched up due to me changing names for security - but it should give you the idea

thanks

**DonkeyOte** · 06-01-2009, 11:12 AM

I can't recall if posted on your other thread or not - I presume it was to do with 3D SUMIF ?

Can you clarify as to how the sample weekly sheets correlate to your Summary sheet - ie which values represent "Style" and which "Vibe".

If I've interpreted correctly you can map the weekly data on "Stream vs Resolver" sheet to the common results tables you have on each weekly sheet (possibly using INDIRECT but this is Volatile), thereafter you can work as before, correct ? (ie base Monthly totals off the Weekly Summary)

(I should add I will be offline shortly so I may not post again until tomorrow, if no one has resolved for you in the meantime)

**nhojflies** · 06-01-2009, 11:51 AM

T+S Total would be "Style" and AOA would be "Vibe"

- I don't think I explained myself well and because I stripped out the formulas and left the values in order to try and simplify the example sheet - it makes it looks like I am working in reverse from what it actually happening!

In the "real" thing - the data is actually taken from the mutliple worksheets starting with "WC.." and being mapped to "Stream vs Resolver" (and various other summary sheets I have left out for simplicity)

The table in "Stream vs Resolver" sources the unititled (but with headers
T+S from Stream AOA from Stream
)
summary tables on all the different sheets starting with "WC -..." and basically just plots those numbers in one big table.

I know in the example sheet there are VALUE and REF errors in one column but this is not actually a problem in the real one - its a result of me changing names for security.

The thing is - this system works fine when you want your table to represent weekly totals as the totals are automatically Counted in the summary tables in each "WC-.." sheet.

However, ideally, rather than just summing all the data for the "week commencing" data which starts in each month - which will include data for days outside the month - for each month I would only sum the data for all DAYS which fell inside the month.

The way the DAYS are represented is by a date in the first column adjacent to the first record recorded for that date on the "WC - ..." sheets

I apologise if this is not well illustrated...

**DonkeyOte** · 06-04-2009, 06:21 AM

Sorry for delay.

In truth I can't analyse your file in any real depth given as you yourself state you have a lot of errors and / or missing named ranges.

The key issue is seemingly that you wish to differentiate monthly values based on individual day as specified in Col A on each Weekly sheet as opposed to by the week commencing date.
This makes perfect sense and is undoubtedly best approach, however, given the way your file is set up this is going to be prove problematic.

As I see it you have 3 immediately obvious choices:

1 - use a complex & Volatile SUMPRODUCT approach

2 - create an identical annual matrix on each weekly sheet so as to allocate the daily values into the correct monthly column.
Given the tables are identical and located in the same place on each Week sheet you can simply 3D Sum the cells across the sheets on the Monthly Summary matrix (eg: =SUM(firstsheet:lastsheet!Z1)

3 - use a UDF (VBA User Defined Function)

Options 1&2 would certainly necessitate you store a date against each transaction on the Weekly sheets and not just the first transaction of each daily batch. You should really do this as a matter of course.

**nhojflies** · 06-08-2009, 05:39 AM

Thank you for you continued support with this project - apologies for the scrappiness of the sample data but your appraisal of my goal is spot on.

The reason I've not gone down the route of having the date next to each transaction (as would be necessary if using your suggestions for 1 and 2) is that the sheet is updated not just by myself but by multple users - meaning that I want to keep the amount of updating to a minimum in order to minimise the chance of error and the amount of effort in updating.

This is why, ultimately I went for the option of basing month tables on the sheets wholesale.

However option three intrigues me - could you expand on this - I have no experience of using VBAs - would it amount to additional work for whoever updates the spreadsheet with a new transaction?

Thanks again

**DonkeyOte** · 06-11-2009, 06:47 AM

Apologies for delay - I've been struggling to get the time to look at this properly.

Based on your sample file you could perhaps use a UDF along the lines of:

Please Login or Register  to view this content.

The above (stored in a Module) would be called from the Monthly table as so:

Please Login or Register  to view this content.

where...

Column AE holds Month, Row 28 holds Name Headers, Row 29 holds Type Headers.

I've attached an updated version of your file with the UDF in place... I added a WC 30th March sheet also to illustrate the March/April dates on that sheet being filtered accordingly in results matrix.

**nhojflies** · 06-15-2009, 07:06 AM

Thanks for your message - this looks very promising.

Unfortunately I am getting a #NAME error when I try to use the function. I'm new to UDFs so its very possible I'm doing something very simble wrong. However I can't figure out what it is.

I've created the module and it shows up in the function list under User Defined functions but any time it is mentioned in a cell it returns an Invalid Name Error. The Debugger points to the Function name. Even when I just try and use the function from the function list, (where it gives the full address of the formula, including the workbook name) I get the same result.

**DonkeyOte** · 06-15-2009, 07:54 AM

Where have you stored the Function and from where are you calling it - it sounds as though you're calling the Function from a different workbook to that in which the Module is located... ?

**nhojflies** · 06-16-2009, 04:32 AM

Created it in the same workbook and can access it when only the workbook I'm working on is open. When I look at it in the function list the title of it is "Copy_of_Service_Desk_Records.xls!Module3.MONTHLYTOTALS", which seems right - the first bit is indeed the workbook I'm working in...

**nhojflies** · 06-19-2009, 09:11 AM

DonkeyOte and I had a private discussion - below is the final VBE solution which works along with (

Please Login or Register  to view this content.

in AG30 where AE30 is the month, AG28 is the stream and is EG29 is the resolver - ) -populated across the whole table.

Thanks again DonkeyOte!

Please Login or Register  to view this content.

Creating Monthly Data Table from Weekly Data Table

LinkBack

Thread Tools

Rate This Thread

Display

Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Re: Creating Monthly Data Table from Weekly Data Table

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions