Adding text fields to the values columns in pivot table.

[Sherwood Botsford]

I've run into this several times.

I have a table that is filled with inventory.
Abbreviation, Pot size, height, location, count, condition Common name (lookup on Abbreviation)

Now I make a pivot table to summarize with rows based on common name, row sub headings for pot size. So I end up with

Alpine Fir
T2 65
T5 130
Balsam Fir
....

No biggie.

But I'd like to have some of the text data carry over. E.g. What is the Abbreviation for Alpine fir? What is the condition (word ratings) for this batch.

At present what I do is create a field in the original table that concatenates abbreviation, pot, condition, height, and use that for my subrow heading. Looks sloppy.

Is there a better way to get text into a pivot table or am I barking up the wrong tree.

 

[jeffreyweir]

Hi Sherwood. Can you upload a file showing what you've got so far?

From memory you're on a mac, which might rule the following VBA solution out. You could use the 'Input message' functionality of data validation, and populate it dynamically with the relevant information pertaining to the underlying PivotCell heading, so that when users click on the cell, more info displays.

 

[Sherwood Botsford]

Yeah, I'm on a mac. The data has to print, be cut and pastable. Ideally the pivot table is exported as CSV, then imported into our accounting program.

Another solution occurred to me: I can fill in columns adjacent to the PT and do lookups from the plants table. But it's clunky.

Gotta love excel. So powerful, but quirky and odd bits missing.