Results 1 to 15 of 15
  1. #1
    Lounger
    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 x-axis 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 x-axis 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 x-axis. I wonder if there is a way to get the x-axis to automatically reflect the data in the Pivot table and to then change these numbers to reflect the more meaningful names.

    Cheers

    Angus

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 X-data to the pivot table "x" column

    Steve

  3. #3
    Lounger
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    Lounger
    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
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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
    Attached Files Attached Files

  7. #7
    Lounger
    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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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
    Attached Files Attached Files

  9. #9
    Lounger
    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

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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
    Attached Files Attached Files

  11. #11
    Lounger
    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

  12. #12
    Lounger
    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

  13. #13
    WS Lounge VIP sdckapr's Avatar
    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
    Attached Files Attached Files

  14. #14
    Lounger
    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

  15. #15
    WS Lounge VIP sdckapr's Avatar
    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 non-zero rather than just counting or summing them. The technique works well and involves no macros.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •