S
S Davis
Hi there,
I have a confusing problem with a PivotTable I have here. The
PivotTable source data spans 43 columns long - a Query from Access (A -
J), set formulas (K - AL), and a second query from Access which ias
based on a link table of this sheet covering the range A - AL.(from AM
- AO), with two additional column calculations to the right.
Stick with me here
In order to get the necessary data for the
PivotTable refresh, I must recalculate the workbook, refresh the first
query (A - J), recalculate the worksheet for the Link Table, and then
refresh the second query (AM - AO). Finally, recalculate thw worksheet
once more.
Both the first and second queries contain dates brought in from Access.
I noticed that the first query brings the dates in as dates, but the
second query brings the dates in as text, so I have a column adjacent
to these text dates with =DATEVALUE(TextDates) copied down. Now with
any amount of refreshing and recalcuiating I can always group data
(into Months/Days) from the first query without issue. Also,
immediately after creating a PivotTable/Chart from this dataset I can
also group the data from the second query and/or the adjacent
=DATEVALUE column. BUT, once the second query is refreshed and the
workbook calculated, bringing in new text dates, I can no longer group
this column and get an error message. Additionally, even the =DATEVALUE
column can not be grouped. If I delete the PivotTable/Chart however and
redo it I have no problem grouping the data again.
So what gives?
Sorry this is so long, thanks for sticking with me
-SD
I have a confusing problem with a PivotTable I have here. The
PivotTable source data spans 43 columns long - a Query from Access (A -
J), set formulas (K - AL), and a second query from Access which ias
based on a link table of this sheet covering the range A - AL.(from AM
- AO), with two additional column calculations to the right.
Stick with me here
In order to get the necessary data for thePivotTable refresh, I must recalculate the workbook, refresh the first
query (A - J), recalculate the worksheet for the Link Table, and then
refresh the second query (AM - AO). Finally, recalculate thw worksheet
once more.
Both the first and second queries contain dates brought in from Access.
I noticed that the first query brings the dates in as dates, but the
second query brings the dates in as text, so I have a column adjacent
to these text dates with =DATEVALUE(TextDates) copied down. Now with
any amount of refreshing and recalcuiating I can always group data
(into Months/Days) from the first query without issue. Also,
immediately after creating a PivotTable/Chart from this dataset I can
also group the data from the second query and/or the adjacent
=DATEVALUE column. BUT, once the second query is refreshed and the
workbook calculated, bringing in new text dates, I can no longer group
this column and get an error message. Additionally, even the =DATEVALUE
column can not be grouped. If I delete the PivotTable/Chart however and
redo it I have no problem grouping the data again.
So what gives?
Sorry this is so long, thanks for sticking with me
-SD