Displaying Text, not numbers, in Pivot Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all. I posted this in the general section but I think it also could
apply to here since I am new with using Pivot Tables.

Currently I have a database in excel which has been made into a pivot table.
It is just a simple catalog of some books. The row fields are organized as
category, field, sub field and then title. The data items are a brief
summary, catalog number, and where the book is currently located (in the
office or someone else has it).

My problem is all of the items in the data field are currently being
displayed as numbers, which are all 1s since it gets that specific with the
title included as a row field. I am wondering if there is anyway to display
these 1s as the actual text that the value is on the original worksheet where
all this information has been input. In other words, the brief summary
actually being displayed on screen.

Now this is a solution but I would prefer the table to be a bit more
interactive so it does not become overwhelming with all the summaries on
screen, in other words being able to click on the summary and have it come
up. Well this is possible by double clicking, that brings you to a new sheet
with all of the information on that entry but there is no way to easily get
back to the pivot table. Is there anyway to include some kind of button
directly on the spreadsheet to link you back?

I was thinking comments would be a good idea since they become hidden, and I
found a macro that would be a good idea which I found on this site:
http://www.mvps.org/dmcritchie/excel/ccomment.htm and is described as Macro
to populate comments in a range with text values of another range
(#addcomments)

Macro posted by Dave Ramage to obtain cell comment values for a single
range from the text values of another matching single range (based on cell
count of 1st range). Modified to use TEXT value which is the displayed value
instead of value.

But that does not work since the ranges are not the same and stuff will be
added to this spreadsheet as time goes on.

Well any help would be appreciated. I am kind of new to Excel and I figure
these would be pretty simple questions to answer for someone who is familiar
with the program.

Thanks if you were able to read down here! I appreciate your time.
 
LTL,FTC,

Pivot tables are numeric. But what you can do is just use one of the fields as a data field in your
pivot table. Double click on the 1 corresponding to the item of interest, and Excel will generate a
new sheet with all of the text data items associated with that item.

HTH,
Bernie
MS Excel MVP
 
Hey Bernie, thanks for the response!

Unfortunately I had been playing around with this, and yes this is a type of
solution. My only concern is that the users useing this are not going to want
to tab through tons of spreadsheets. Is there anyway I can attach a macro or
something that makes it possible to go to the original spreadsheet in the new
sheet that is generated with the line of information?

Thanks again for the response, I appreciate any ideas towards this.
 
Back
Top