Help!

Displaying Text, not numbers, in Pivot Table

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> New Users RSS
Next:  Elapsed time when separate cells contain time and..  
Author Message
Longtime listener first t
External


Since: Jan 19, 2007
Posts: 13



PostPosted: Wed Mar 07, 2007 5:49 am    Post subject: Displaying Text, not numbers, in Pivot Table
Archived from groups: microsoft>public>excel>newusers (more info?)

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.
Back to top
Bernie Deitrick
External


Since: Apr 02, 2004
Posts: 1786



PostPosted: Wed Mar 07, 2007 3:12 pm    Post subject: Re: Displaying Text, not numbers, in Pivot Table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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


"Longtime listener first time caller"
wrote in message
> 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.
Back to top
Longtime listener first t
External


Since: Jan 19, 2007
Posts: 13



PostPosted: Thu Mar 08, 2007 6:44 am    Post subject: Re: Displaying Text, not numbers, in Pivot Table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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.

"Bernie Deitrick" wrote:

> 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
>
>
> "Longtime listener first time caller"
> wrote in message
> > 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.
>
>
>
Back to top
whoslacks



Joined: Mar 29, 2007
Posts: 1



PostPosted: Thu Mar 29, 2007 8:51 pm    Post subject: Re: Displaying Text, not numbers, in Pivot Table [Login to view extended thread Info.]

I have an ugly workaround but it will work. First you take your raw data (the source for the pivot table) and import it from the spreadsheet in to Access as a table. Second you create a query that pulls all the same fields. Run the query and then select pivot table view from the drop down menu at the top. In access you'll get the same type of screen for creating a pivot table however when you pull your text information into the data field (the place where excel usually tries to sum and you end up with 1's) the values will actually be there. If you click the export to excel link it will not work... However! if you do Control A in access and then do control V in Excel it will do what you ask.

Ugly... but it works... that being said... if anyone knows how to make this actually work in Excel without all the run around... please share!
Back to top
hmuhammad



Joined: May 02, 2007
Posts: 1



PostPosted: Thu May 03, 2007 2:11 am    Post subject: Re: Displaying Text, not numbers, in Pivot Table [Login to view extended thread Info.]

Which version of Access worked for this?
(I tried it with Access 2000; with which it did not work--it only display the 1's).

Hasan
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> New Users All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum