Results 1 to 15 of 15
Thread: Graph of Pivot Tables (97 SR2)

20021119, 09:28 #1
 Join Date
 Feb 2001
 Location
 Glasgow, Scotland
 Posts
 46
 Thanks
 0
 Thanked 0 Times in 0 Posts
Graph of Pivot Tables (97 SR2)
I have created a graph from a Pivot table. Unfortunately the xaxis was in numbers and I wanted to change this to more meaningful data which I did by changing the number to a more meaningful title, I did this comparison manually on the same page as the pivot table and getting the xaxis to point to this value. However the data has changed for this week and some of the numbers which appeared in the data last week nolonger appear in the PIVOT table and my graph has an invalid xaxis. I wonder if there is a way to get the xaxis to automatically reflect the data in the Pivot table and to then change these numbers to reflect the more meaningful names.
Cheers
Angus

20021119, 09:57 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Graph of Pivot Tables (97 SR2)
Some options:
1)add the desired values to your data table and then add this field to the pivot table?
2) add a calculated field to the pivot table for the values
3) "link" the graphed Xdata to the pivot table "x" column
Steve

20021119, 10:46 #3
 Join Date
 Feb 2001
 Location
 Glasgow, Scotland
 Posts
 46
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph of Pivot Tables (97 SR2)
Steve thanks for your help,
However, I am not sure how big the base data will be so I would prefer one of the other two options. On the calculated field, I am not sure what to do.
I have a field called "code" which has the numbers 1751, 1752, 1753 etc. I want to change this to Management, Systems, Mechanical. Looking at the calculated field I do not understand how to do this. This also has to take account that next week maybe only 1751 and 1753 will be in the base data. So I do not know what to put into the field name and formula sections of the calculated field option?
The linked data may be a better option because I forgot to mention last time that I had to manually change the graph source to take account of the pivot table change. However I do not know how to do this.
cheers
Angus

20021119, 17:42 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Graph of Pivot Tables (97 SR2)
1) you can link using a lookup table (vlookup).Make a list of "codes" and "desc". Lookup the X from the pivot table and get the code. (You could add this "lookup" as a field in the original data)
2) To account for changing pivot size, for the chart:
either use variable range names (with OFFSET) or use a macro
Post an example, it will be easier to walk you thru if you still have questions.
Steve

20021119, 21:30 #5
 Join Date
 Feb 2001
 Location
 Glasgow, Scotland
 Posts
 46
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph of Pivot Tables (97 SR2)
I have tried to link from the pivot table but the calculated field won't let me. As you have suggested I attach the file. What I am trying to do is work out the disciplines booking to UA85.
Cheers
Angus

20021119, 23:16 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Graph of Pivot Tables (97 SR2)
I did this without the pivot table. Added a column to data and with Autofilter you get the disciplines of the Job No.
I am still not sure what you want. Your pivot table gets a lot more data than you are plotting. What are you trying to do?
Steve

20021120, 07:25 #7
 Join Date
 Feb 2001
 Location
 Glasgow, Scotland
 Posts
 46
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph of Pivot Tables (97 SR2)
What I was trying to do was have a system whereby the person only required to feed in the data and then the graph would appear. they would therefore not have to worry about adding information to the data received. However I think the solution that you have proposed should not be beyond their capabilties.
Sorry Steve now that I have looked at this in more detail I thought that the graph still displayed the disciplines as a group rather than as a list of individual slots. What I require is to present the information with the view of which of the disciplines are booking to the number. By adding the column suggested this will give the codes. However there is then still the problem of getting the graph then to only reflect the totals of those disciplines which have a value as this is likely to change on a week to week basis.
You are also correct that there is more data being collected however other than hiding the data in the pivot table I have not been able to select only UA85.
Thanks for the help Steve
Angus

20021120, 10:44 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Graph of Pivot Tables (97 SR2)
If you are looking for totals by DISCIPLINE text try this.
If you add more entries to table the chart should automatically reflect it
If you have more more codes/disciplines copy the rows in the summary table down (they ranges will grow automatically and enter into the first 2 columns.
Steve

20021120, 10:59 #9
 Join Date
 Feb 2001
 Location
 Glasgow, Scotland
 Posts
 46
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph of Pivot Tables (97 SR2)
Steve,
This is certainly a much more elegant way of presenting the data. I will take a little more time to digest what you have put together.
Thanks for spending the time to help.
Cheers
Angus

20021120, 11:35 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Graph of Pivot Tables (97 SR2)
I attached the last one from a file before i did my final save. There are some differences. Use this one.
Steve

20021120, 12:35 #11
 Join Date
 Feb 2001
 Location
 Glasgow, Scotland
 Posts
 46
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph of Pivot Tables (97 SR2)
Steve sorry to bother you again.
However I decide to take out some of the information from the main sheet to get below the 100K level. I have had a go at now reintroducing the additional columns from the main spreadsheet. I thought that by inserting the columns the links between summary and current would be maintained. However when I input the additional columns the links in summary go to #REF. Is there any quick way in which I can input these additional columns and retain your links. There are 17 columns in total
Uid PayrollNo WeekNo WkEnd JobNo BasicHrs OTmf OTsat OTsun GenComments RWcode RWhrs RWcomments BasicOK OTOK Discipline Code
Cheers
Angus

20021120, 13:54 #12
 Join Date
 Feb 2001
 Location
 Glasgow, Scotland
 Posts
 46
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph of Pivot Tables (97 SR2)
Hold that last post, worked out that the column I was putting in as A did not have numbers and as a result was screwing up the range name DataTable.
I still have a problem with the chart in that I wanted to show the information with purely UA85 entries. I am sure that the column with the number of entries can be changed to work out how many of these are UA85 entries. Have tried to replicate the =COUNTIF(INDEX(DataTable,0,17),$A2) by adding to the job no, which is column 5, ie (INDEX(DataTable,0,5),UA85) but I can't get this to work.
Cheers
Angus

20021120, 17:16 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Graph of Pivot Tables (97 SR2)
I added another section for the UA85 (I had to expand some of the range names.
Actually in F1 (the cell cell) you can enter in any item and the jobchart will reflect it for that item.
If you are going to insert a lot, you can put a col # (=column()) in one of the rows and link to that, but as you can see it can get busy. Try the techniques and use what you like.
Steve

20021120, 18:12 #14
 Join Date
 Feb 2001
 Location
 Glasgow, Scotland
 Posts
 46
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Graph of Pivot Tables (97 SR2)
Thanks once again, I had tried using array formulas with the countif mechanism without success.
Cheers
Angus

20021120, 18:54 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Graph of Pivot Tables (97 SR2)
In principle array formulas can get you the values.
The tough part is the "resorting" of the data to get JUST the items that are nonzero rather than just counting or summing them. The technique works well and involves no macros.
Steve