Hello,
I'm trying to automate a manual process I run every week. I export data from a database that includes hundreds of rows to an excel file. One of the columns in the rows is a "hours" column. I subtotal that column and store that value in a cell in another Excel file. I've gotten to the point where I can use a Webquery to pull the data down in to Excel. I've made a pivot table to sum up the hours and then the final table uses a formula to reference the total hours from the pivot table in the cell I need it. The problem I have is that over time, the amount of data downloaded gets rather large. I'd like to configure the query so that is filters the downloaded results by a date range. I know how to do this, part - but my problem is that each week, if the data downloaded a filtered to the current weeks data, then the previous data is wiped out of the final table. So, my question is... Is there a way to take the "total hours" for a week from the pivot table and store it in a cell as a permanent value. I know I can copy/paste as values, but I want an automated method. I want to just open the excel file, have it "refresh data" and my final table is updated without losing the previous data. Is this possible?
Thanks,
Mr. Z
Bookmarks