Here's a tough one (I think). I'm trying to build some clean aggregate data out of some query results, and I keep spinning between pivots and lookups, neither of which seem to work. The end goal is a summary sheet that drives a dashboard sheet with graphs. Here are some specifics:
- I have several sheets that are populated by ODBC queries. These queries are expected to be ran monthly.
- Query results are rows of summary data that are ran on a server and written as a DB entry. The run-times of these roll-ups do not line up with the updates in the Excel sheets.
- Each sheet's list of roll-up data may have more than one entry per month (this is the part I'm stuck on). I need to be able to sum up column values across rows from the same month, but not specifically the same timestamps.
- Data from each of the ODBC sheets do not cross (i.e. I only need to summarize each sheet with respect to itself, not with other sheets).
- The list of rows for each sheet will continue to grow (i.e. no fixed number of results returned).
- I'm trying to keep the summaries for all sheets on one tab so that I can reference it excusively for my dashboard view. [This isn't a core requirement, just makes my organization efforts a little easier]
- I have Office 2007 but the machine this report lives on is still using 2003. We expect it to be upgraded later in the year, but I can't wait that long. This means things like multiple condition SUMIF
- I'm okay with (and happy to use) VBA - but I'm not familiar with the syntax. I do have a fairly strong programming background, so I'm not adverse to learning whatever I need to if macros/VB coding is the solution.
I really could use some help here. The clock is ticking and I feel like I'm at the "staring at the wall" phase... I've included an attachment of sample (dummy) data to represent what I'm trying to do. Any and all assistance is appreciated!
Bookmarks