Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating auto Updating Pivot Table linked to external data.
Hi All,
I want to be able to have an excel pivot table that automatically updates with data once a week after our invoice run. Our invoicing run, runs from Sunday to Saturday and but i only require a week ending figure on the Saturday. I can do this at the momment by impoting my csv file into my excel spread sheet manually and manually change the date field in my file to get my pivot table to recognise the dates as auctual dates so that i can the groups them by week ending in the pivot table. Hope that makes sense. I have also tried just linking a pivot table to external data using ms query which again works but also the data in ms query lookes like it has imported as dates when i try to group my pivot table via w/e dates it says can't group that selection. So to summaries does any one know how to get datas into excel via external data as a pivot table so you can group the date field. Many thanks for any help. Regards Mike |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating auto Updating Pivot Table linked to external data.
The issue that you are running into is that the data in your database is not
being stored as a date but rather it is being stored as a text string or number. What you want to do is to coerce the value to a date. How you go about doing that depends on the type of database you are using. If there is someone where you works who uses that database and knows a bit of SQL they could probably help you fix up your query to return actual dates. As for the automatic refresh I would be more inclined to just refresh it manually. If you are hooked directly to the database it is just a matter of hitting refresh. -- HTH... Jim Thomlinson "mike" wrote: Hi All, I want to be able to have an excel pivot table that automatically updates with data once a week after our invoice run. Our invoicing run, runs from Sunday to Saturday and but i only require a week ending figure on the Saturday. I can do this at the momment by impoting my csv file into my excel spread sheet manually and manually change the date field in my file to get my pivot table to recognise the dates as auctual dates so that i can the groups them by week ending in the pivot table. Hope that makes sense. I have also tried just linking a pivot table to external data using ms query which again works but also the data in ms query lookes like it has imported as dates when i try to group my pivot table via w/e dates it says can't group that selection. So to summaries does any one know how to get datas into excel via external data as a pivot table so you can group the date field. Many thanks for any help. Regards Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating external data sheet causes loss of data on linked pages | Excel Worksheet Functions | |||
automating external linked pivot table | Excel Discussion (Misc queries) | |||
Updating Pivot Table Data automatically from external workbooks | Excel Discussion (Misc queries) | |||
Auto updating pivot tables using external XML data | New Users to Excel | |||
Pivot table using External data | Excel Discussion (Misc queries) |