I am using Excel 2007 and using several pivot charts which reference the same table of data. I change the data daily, copy and pasting the data from another source. I have several questions:

  1. Is it possible to change the grouping on the table so that I can view the data differently in different pivot tables? For example, one graph views the data by hour and another by month and another by day. It seems that when I change the grouping for one graph, it changes it on every one. My solution has been to copy and paste in the data into three separate tables for data with each one having different groupings.

  2. Can I somehow create a reference to a date for the pivot tables so that I don’t have to change the dates on every graph drop down? I have about 30 tables and have to change the date each day to the new date and that leaves room for much human error. Another solution would be to have the graphs reference the same data section on the pivot table, is that possible?

  3. When I copy and paste in the data each day, the date ranges change on some of the pivot tables, adding ranges on the bottom and if I don’t notice, it messes up my data. How can I prevent this?

  4. I have the pivot tables linked into a Word document. This may be a Word issue, but every Word document now turns read only after a few saves. It’s very random and they re-imaged my system, rebuild my profile and loaded the OS again, none fixing the problem. I read online but no one had a solution.

  5. Auto Refresh will work on the initial copy and paste into Word from Excel. Any updates to Excel is reflected in Word BUT if I close and open the document, initially the updates will occur upon opening the document but any additional changes are not reflected. It then requires a new copy paste or closing the document and reopening it. How can I get it to auto refresh every time?

1 Answer 1

  1. I would not change the grouping or sorting in your data area. Let the pivot tables do that. When you change one pivot table, I don't think you will see the others changing then.

  2. The solution to the print dates that I used to use was this - I had a cell or an area where I entered the dates and any other print data I wanted to see in the header or footer - titles, report names, etc. I did not make the date cell a formula - I entered it directly. (For instance, if I am printing today, but the official print date was yesterday and I am printing extra copies or a corrected version.) All the places I needed to see the date were a reference to those single cells.

  3. This shouldn't mess up your date, or your data. It could mess up your table. One solution would be to give a source data range for the pivot table that included blank rows. Then, filter the blank rows out on the pivot table.

  4. and 5. I haven't played with document linking in Excel 2007 or 2010 - but I KNOW that linking Excel sheets like this in the past was never a good solution. It was always resource intensive. It would create effects that we did not want - and it always created more work. So the best solution we found was NOT to paste a link at all - but to paste the desired workbook area as a picture. This was relatively fast. If the links worked, it would be slower - but since the links between Word and Excel created so many problems - pasting as a picture always saved time and heartache in the long run. You may want to try it. I reliably used that solution for multi-million dollar project reports.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .