Freehand comments INSIDE a pivot table 1

[Mouldheels]

I have a pivot table that compares budget with actual and produces a variance.

I want to add commentary on each line to explain the reason for the variance.

The commentary has to keep up with changes in the pivote table so external commentary is not acceptable.

 

[SkipVought]

hi,

Plese post a representative sample of your PT and the corresponding comment(s), being sure to VIEW in PREVIEW and modify as required to make the display understandable. You might need to use TGML TT tags to maint columnar spacing.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mouldheels]

Here in simplified form is what I want in my pivot table

BC CC Post Surname Commentary M1 M2 M3 Budget Actual Variance
ATJ1 21205 1334522 MaXXXXX "Here be dragons" 12,895 12,895 12,895 38,687 38,684 2

OK this is not a banker!!

 

[SkipVought]

You didn't look at the results of your post, did you?

So you want the occurrence of these FOUR Fields/values to correspond to your comment, right? I added another comment, with corresponding ROW data for illustrative purpose.
[tt]
BC CC Post Surname Commentary
ATJ1 21205 1334522 MaXXXXX Here be dragons
ATJ1 21205 1334522 SkipVought Fire breathing
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mouldheels]

Yes you have it there

 

[SkipVought]

So here's the process...
[tt]
1. Query the Source Data to return fields BC CC Post Surname with distinct values, in a NewSheet.

select distinct BC, CC, Post, Surname
from [Your Sheet Name$]

2. Put you Commentary in this table

3. Query NewSheet and your other sheet as a join in a new PivotSource sheet

select a.*, b.Commentary 
from [Your Sheet Name$] a, [NewSheet$] b
where a.BC = b.BC
  and a.CC = b.CC
  and a.Post = b.Post
  and a.Surname = b.Surname

4. Redo your PT and just also include the Commentary field.
[/tt]
faq68-5829


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mouldheels]

Hi Skip

That is an interesting solution.

However it adds a new level of complexity to my knowledge.

If I had any sense I would renew my knowledge of Access and store my data there.

I am sometimes working with over 500,000 lines of data in Excel and the system is starting to creak!!

However I am getting close to retirement and don't want to exercise my brain too much by leaning new applications.

Is there a simpler solution?

 

[SkipVought]

This IS a very simple solution.

Once it it set up, it's just refreshing 2 querytables once commentary is changed.

My 70 year old brain can get it, and I am also close to that point.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mouldheels]

What I am currently doing is creating another field in the data, like

=Surname&PositionNo

This is useful in that it creates unique value for each person/job possibility.

I then add this as my commentary field in the pivot table and colour it white so that it can't be seen by any uninitiated viewer.

The the comment now says "SurnamePositionNo", (e.g. Whittaker111222333) for each row in the pivot table.

When I want it to show a comment, I change the colour and edit the field directly in the pivot table, free hand like this

"Whittaker is on Maternity leave" now replaces "SurnamePositionNo"

This has the advantage that anyone can do the same if they are in the know, so my boss can add his own comments too.

Having the comments edited in a seperate data table, as suggested by you, is an added complication.

HOWEVER

My method has some disadvantages:

1). Every Comment has to be unique (so I leave the persons name in from the original formula
So I can't say "is on Maternity leave" in more than one cell. it has to be "Brown is on Maternity leave" and Whittaker is on Maternity leave".
2). If people receive pay on more than one cost code, this cost code has to be included in the formula =Surname&PositionNo&CostCode.
3). If two people with the same surname have the same position number at different time of the year you have to add another unique identifier such as "EmployeeNumber" to the formula.

Like your method, it takes a little time to get used to, but it is a doddle to review each row in order, once new monthly data is added. That is a great time saver rather than looking at each variance when a query arises and then have to gather all the information from the basics.

70? I wondered why you spent so much time here. Thank you again from me and I'm sure from everyone elase you have helped over the years. You are a star.

 

[SkipVought]

Having the comments edited in a seperate data table, as suggested by you, is an added complication.

How is this any MORE of a complcation than the contortions you have posited.

In a separate data table, every commentary IS UNIQUE.

If you wanted to "automate" the commentary update process IN THE PT, it could be done with some fancy VBA.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mouldheels]

Hi Skip,

I agree it is a bit contorted but it has come together through applying my existing knowledge.

Which is why I was searching for another possible solution.

I am an accountant, not a programmer or systems analyst.

One of the wonders of computing is that there are multiple solutions to the same problem.

The trick is finding the solution that your skills and experience can cope with!!

I look on here and find nice simple solutions and solutions that require complex VBA skills with little in between.

If I were 40 I would jump at the chance of deploying new skills!!

However. I will look at your suggestions and play about with then for a day or so.

Thanks once again for your help.

 

[SkipVought]

Fair enough.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]