Tag: excel

Posted on
by

I’ve never done much work with groups in PivotTables but I have used PivotTables a lot. One thing I like do is make a simple PivotTable and chart and copy this sheet multiple times, editing as appropriate. I then link charts in Word to the data in Excel. This makes it easy to go back and make modifications

I’ve been working on a sheet this morning and, to my horror, have discovered that my previous efforts are being altered as I update subsequent efforts. It’s a bit like that scene in Labyrinth where Sarah discovers her lipstick marks have been changed. Of course, when you are working on lots of charts your memory plays tricks on you so I had to actually CHECK that this was really happening.

It really was.

A quick Google reveals that:

To help improve performance and reduce the size of your workbook, Excel automatically shares the PivotTable data cache between two or more PivotTable reports that are based on the same cell range or data connection

Usually this isn’t a problem unless you use, for example, grouping. In that case it does the grouping in the shared cache, thereby updating all your previous tables and charts. “Your mother is a fragging aardvark,” indeed!

This article explains how to unshare the cache but I don’t want a separate cache for each chart so I’ll settle for unlinking and embedding the charts in Word instead.

Posted on
by

Found a new annoyance in Excel. I am analysing some data in Excel that’s been extracted from CiviCRM. Most of the data was submitted via webform. I easily managed to pull the URL out of the data to view the original webform submission. However, clicking the link in Excel I get an error:

Microsoft Excel Error
Unable to open URL. Cannot download the information you requested.

A quick Google brings me to this site. Of course, you need to be an authenticated user to view the web form but Excel is unable to authenticate so it just fails. Well, in reality it just sees a 404 and tells you the link is broken. Once Excel makes this decision it won’t pass the URL to the browser to open. Great.

So, another quick Google about stopping Excel validating links brings me to Stack Exchange. Of course, I can’t comment on SE due to their chicken/egg “reputation” rules so the only option I have for drawing these two threads together is this blog post.  Hope it helps!