Page 1 of 4 123 ... LastLast
Results 1 to 15 of 55
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    hi all,

    i have the attached file which contain sample of my data using pivot table, what i want to do is add two items highlighted in pink using conditional formula - highlighted in yellow- that will output the data under each one.

    dubdub
    TIA
    dubdub

  2. #2
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    sorry, here is the attached file.

    dubdub
    TIA
    dubdub

  3. #3
    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
    From the output it appears that the source is corrupted or setup wrong...

    If you want the pivot table to extract the data differently, you probably will have to change the way it is setup in the source range. Could you attach a file with the source range, it is not part of the file you attached...


    Steve

  4. #4
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' date='2010-01-10 13:49' timestamp='1263131399' post='818489']
    From the output it appears that the source is corrupted or setup wrong...

    If you want the pivot table to extract the data differently, you probably will have to change the way it is setup in the source range. Could you attach a file with the source range, it is not part of the file you attached...


    Steve
    [/quote

    hi steve,
    the output data in the file is added manually,it is not yet part of the pivot table,and i am looking for the formula that can produce this output and can be used in pivot table formula option/section to produce that output, i hope i was able to clarify my request.

    dubdub
    TIA
    dubdub

  5. #5
    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
    If you are going to have the Pivot table and you want a formula, in F5:
    =IF(OR($C5<VALUE(LEFT(TRIM(D$4),4)),$C5>VALUE(RIGH T(TRIM(D$4),4))),"",D5)

    Copy f5 to F5:G24

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    If you are going to have the Pivot table and you want a formula, in F5:
    =IF(OR($C5<VALUE(LEFT(TRIM(D$4),4)),$C5>VALUE(RIGH T(TRIM(D$4),4))),"",D5)

    Copy f5 to F5:G24

    Steve
    hi Steve,

    i used the formula to define and to output the data for a new item in the pivot table called 10-14 but i get "#value!", the formula works fine if applied outside the pivot table. how can i have it work the same within a pivot table for an item. Thanks for your patient.

    dubdub
    Attached Files Attached Files
    TIA
    dubdub

  7. #7
    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
    You asked for a formula outside the pivot table. As I mentioned in my first reply, if you want to change the pivot table you will have to change the source. I don't know what the formula should be for the source since your files do not have the source for the pivot table. I would imagine thought that your column field of 2010-2014 should NOT have data listed if the the date is <2010 or >2014 and that seems to be a "corruption" in your source data.

    But I can only speculate on the cause and can not specify a solution unless I can actually see the source data for the pivot table...

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    You asked for a formula outside the pivot table. As I mentioned in my first reply, if you want to change the pivot table you will have to change the source. I don't know what the formula should be for the source since your files do not have the source for the pivot table. I would imagine thought that your column field of 2010-2014 should NOT have data listed if the the date is <2010 or >2014 and that seems to be a "corruption" in your source data.

    But I can only speculate on the cause and can not specify a solution unless I can actually see the source data for the pivot table...

    Steve
    hi Steve, and thanks again for your patient, attached is a source data sample.
    Attached Files Attached Files
    TIA
    dubdub

  9. #9
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    You asked for a formula outside the pivot table. As I mentioned in my first reply, if you want to change the pivot table you will have to change the source. I don't know what the formula should be for the source since your files do not have the source for the pivot table. I would imagine thought that your column field of 2010-2014 should NOT have data listed if the the date is <2010 or >2014 and that seems to be a "corruption" in your source data.

    But I can only speculate on the cause and can not specify a solution unless I can actually see the source data for the pivot table...

    Steve
    hi Steve, and thanks again for your patient, attached is a source data sample.
    TIA
    dubdub

  10. #10
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    You asked for a formula outside the pivot table. As I mentioned in my first reply, if you want to change the pivot table you will have to change the source. I don't know what the formula should be for the source since your files do not have the source for the pivot table. I would imagine thought that your column field of 2010-2014 should NOT have data listed if the the date is <2010 or >2014 and that seems to be a "corruption" in your source data.

    But I can only speculate on the cause and can not specify a solution unless I can actually see the source data for the pivot table...

    Steve
    hi Steve, and thanks again for your patient, attached is a source data sample.
    TIA
    dubdub

  11. #11
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    You asked for a formula outside the pivot table. As I mentioned in my first reply, if you want to change the pivot table you will have to change the source. I don't know what the formula should be for the source since your files do not have the source for the pivot table. I would imagine thought that your column field of 2010-2014 should NOT have data listed if the the date is <2010 or >2014 and that seems to be a "corruption" in your source data.

    But I can only speculate on the cause and can not specify a solution unless I can actually see the source data for the pivot table...

    Steve
    hi Steve, and thanks again for your patient, attached is a source data sample.
    TIA
    dubdub

  12. #12
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    You asked for a formula outside the pivot table. As I mentioned in my first reply, if you want to change the pivot table you will have to change the source. I don't know what the formula should be for the source since your files do not have the source for the pivot table. I would imagine thought that your column field of 2010-2014 should NOT have data listed if the the date is <2010 or >2014 and that seems to be a "corruption" in your source data.

    But I can only speculate on the cause and can not specify a solution unless I can actually see the source data for the pivot table...

    Steve
    hi Steve, and thanks again for your patient, attached is a source data sample.
    TIA
    dubdub

  13. #13
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    You asked for a formula outside the pivot table. As I mentioned in my first reply, if you want to change the pivot table you will have to change the source. I don't know what the formula should be for the source since your files do not have the source for the pivot table. I would imagine thought that your column field of 2010-2014 should NOT have data listed if the the date is <2010 or >2014 and that seems to be a "corruption" in your source data.

    But I can only speculate on the cause and can not specify a solution unless I can actually see the source data for the pivot table...

    Steve
    hi Steve, and thanks again for your patient, attached is a source data sample.
    TIA
    dubdub

  14. #14
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    You asked for a formula outside the pivot table. As I mentioned in my first reply, if you want to change the pivot table you will have to change the source. I don't know what the formula should be for the source since your files do not have the source for the pivot table. I would imagine thought that your column field of 2010-2014 should NOT have data listed if the the date is <2010 or >2014 and that seems to be a "corruption" in your source data.

    But I can only speculate on the cause and can not specify a solution unless I can actually see the source data for the pivot table...

    Steve
    hi Steve, and thanks again for your patient, attached is a source data sample.
    TIA
    dubdub

  15. #15
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    You asked for a formula outside the pivot table. As I mentioned in my first reply, if you want to change the pivot table you will have to change the source. I don't know what the formula should be for the source since your files do not have the source for the pivot table. I would imagine thought that your column field of 2010-2014 should NOT have data listed if the the date is <2010 or >2014 and that seems to be a "corruption" in your source data.

    But I can only speculate on the cause and can not specify a solution unless I can actually see the source data for the pivot table...

    Steve
    hi Steve, and thanks again for your patient, attached is a source data sample.
    TIA
    dubdub

Page 1 of 4 123 ... LastLast

Posting Permissions

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