so i am ok at excel but needed some help as I have no idea how to do this if it will require VBA or not. Basically whats happening right now is I have a large spreadsheet that is sort of like a historical dashboard tracker for me. I have a sql query that dumps the month's data into a worksheet and then I have pivot tables from that sheet. Now whats happening since its near end of month I have over 600k records in that one sheet and it keeps crashing the workbork. What I would prefer to do is modify my sql query to just pull the data for the current day, but then I would need a way to have the aggregate totals for the previous days stored somehow (without any manual work that is) I am not sure if there is a way to copy a temporary value in a certain cell and have it permanently store in a different cell doing an index match? like I'll have a pivot table where the value is always in like B4 and then daily have that value store permanently in a different table with the date. Because I really just need some aggregate totals on the data I am pulling. I am going to keep searching the interwebs but I figured I would toss it out here as well in case you excel gurus here knew how faster. Thanks!
Just to be clear, your main objective is to reduce the number of rows coming into Excel from a sql-db, right? If so, a different SQL Query should be able to do everything for you. Transform the data prior to inputting into Excel. I'm willing to bet that you don't need to do extra work in Excel, just change the SQL query. Can you show the SQL query, or a simplified use case.
Seems like that's what he wants. OP show your SQL like what 'heypartner' said. It would help a lot to get what you need.
I still want to retain the aggregate data info from the previous days for tracking purposes for example on jan 26th - 5000 calls were made jan 27th - 6000 were made and so on so i can at least keep those values from each data but not necessarily have all data stored for the month and just for the day so it doesnt keep crashing heyp - what email are you using nowadays? can I msg you through the board?
It's the same email from forever; you might still have it. Or eamil through the bbs. I probably can't look at it until after work, though. But it does sound like a simple change of the sql query.
I guess you are using the Jet provider to run the SQL from one workbook to the next. Why not just run two queries to find each day's totals? And why not keep the historical data somewhere more stable like Access? You could the. Make queries very easily there and import them using Jet (ADODB).
