Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am stupid and don't know what the heck I am doing wrong on this pivot table!!!
I used to have a full head of hair until I made my pivot table!! I am
trying to do the following: Client Name, Last Shipped,Rep,Control #,File #,Grand Total My list of clients shows all the detail but then when I hide the detail, the: Last Shipped Rep Control # File # are completely blank. I still have a grand total of the detail, which is great but I am trying to Show the most recent Last Last Shipped,Rep,Control #,File #, along with the Grand Total. I am just looking to just show 1 line. This really nice lady Deb tried to help me but I am completly clueless to what she wanted me to do. Tried outlines but when I refresh the data, everything goes out of wack. I am losing hope and cannot find a solution to this. Please help!!! Chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am stupid and don't know what the heck I am doing wrong onthis pivot table!!!
As answered in microsoft.public.excel.misc:
Maybe this will do what you want: Add a new column (LastShip) to the source data, which marks the latest shipment for each customer For example, with customers in column A, and dates in column C, enter this formula in cell D2: =IF(C2=MAX(IF($A$2:$A$200=A2,$C$2:$C$200,"")),"x", "") Substitute your last row for the 200 in the formula. This is an array formula. After you type it, hold the Ctrl and Shift keys, and press Enter. Copy the formula down to the last row of data. Refresh the pivot table, and add the LastShip field to the row area. From that field's dropdown list, leave a check mark only in the X item. Hide Detail for only that field. Does that give you the layout that you need? Chris wrote: I used to have a full head of hair until I made my pivot table!! I am trying to do the following: Client Name, Last Shipped,Rep,Control #,File #,Grand Total My list of clients shows all the detail but then when I hide the detail, the: Last Shipped Rep Control # File # are completely blank. I still have a grand total of the detail, which is great but I am trying to Show the most recent Last Last Shipped,Rep,Control #,File #, along with the Grand Total. I am just looking to just show 1 line. This really nice lady Deb tried to help me but I am completly clueless to what she wanted me to do. Tried outlines but when I refresh the data, everything goes out of wack. I am losing hope and cannot find a solution to this. Please help!!! Chris -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am stupid and don't know what the heck I am doing wrong on this pivot table!!!
Debra,
You got me excited, I actually thought that was the trick. But it is not letting me input the formula in the pivot table. Maybe I am doing something wrong. Listed below are the fields I have in the Pivot table: Client Name Column A Last Shipped Column B (ultimate goal is to show the most recent last ship date) Rep C Control # Column D File # Column E 15 50 51 Grand Total Col F Col G Col H Col I These are all fields that I placed in the pivot table. Could it be I am placing the formula in the wrong spot?? Oh Debra, I hope you come back becasue I think you might be on to something. Thanks for the help. Chris Debra Dalgleish wrote: As answered in microsoft.public.excel.misc: Maybe this will do what you want: Add a new column (LastShip) to the source data, which marks the latest shipment for each customer For example, with customers in column A, and dates in column C, enter this formula in cell D2: =IF(C2=MAX(IF($A$2:$A$200=A2,$C$2:$C$200,"")),"x", "") Substitute your last row for the 200 in the formula. This is an array formula. After you type it, hold the Ctrl and Shift keys, and press Enter. Copy the formula down to the last row of data. Refresh the pivot table, and add the LastShip field to the row area. From that field's dropdown list, leave a check mark only in the X item. Hide Detail for only that field. Does that give you the layout that you need? Chris wrote: I used to have a full head of hair until I made my pivot table!! I am trying to do the following: Client Name, Last Shipped,Rep,Control #,File #,Grand Total My list of clients shows all the detail but then when I hide the detail, the: Last Shipped Rep Control # File # are completely blank. I still have a grand total of the detail, which is great but I am trying to Show the most recent Last Last Shipped,Rep,Control #,File #, along with the Grand Total. I am just looking to just show 1 line. This really nice lady Deb tried to help me but I am completly clueless to what she wanted me to do. Tried outlines but when I refresh the data, everything goes out of wack. I am losing hope and cannot find a solution to this. Please help!!! Chris -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am stupid and don't know what the heck I am doing wrong on t
thanks paul for the help. I tried both ways just in case. oh well back
to the drawing board Chris paul wrote: i amnot strong on pivot tables myself but you need t enter that formula alondside yur original data and then include the new column in your pivot table -- paul remove nospam for email addy! "Chris" wrote: Debra, You got me excited, I actually thought that was the trick. But it is not letting me input the formula in the pivot table. Maybe I am doing something wrong. Listed below are the fields I have in the Pivot table: Client Name Column A Last Shipped Column B (ultimate goal is to show the most recent last ship date) Rep C Control # Column D File # Column E 15 50 51 Grand Total Col F Col G Col H Col I These are all fields that I placed in the pivot table. Could it be I am placing the formula in the wrong spot?? Oh Debra, I hope you come back becasue I think you might be on to something. Thanks for the help. Chris Debra Dalgleish wrote: As answered in microsoft.public.excel.misc: Maybe this will do what you want: Add a new column (LastShip) to the source data, which marks the latest shipment for each customer For example, with customers in column A, and dates in column C, enter this formula in cell D2: =IF(C2=MAX(IF($A$2:$A$200=A2,$C$2:$C$200,"")),"x", "") Substitute your last row for the 200 in the formula. This is an array formula. After you type it, hold the Ctrl and Shift keys, and press Enter. Copy the formula down to the last row of data. Refresh the pivot table, and add the LastShip field to the row area. From that field's dropdown list, leave a check mark only in the X item. Hide Detail for only that field. Does that give you the layout that you need? Chris wrote: I used to have a full head of hair until I made my pivot table!! I am trying to do the following: Client Name, Last Shipped,Rep,Control #,File #,Grand Total My list of clients shows all the detail but then when I hide the detail, the: Last Shipped Rep Control # File # are completely blank. I still have a grand total of the detail, which is great but I am trying to Show the most recent Last Last Shipped,Rep,Control #,File #, along with the Grand Total. I am just looking to just show 1 line. This really nice lady Deb tried to help me but I am completly clueless to what she wanted me to do. Tried outlines but when I refresh the data, everything goes out of wack. I am losing hope and cannot find a solution to this. Please help!!! Chris -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am stupid and don't know what the heck I am doing wrong onthis pivot table!!!
The formula goes in the source table, not the pivot table. I've added a
sample file to my web site: http://www.contextures.com/excelfiles.html Under PivotTables, look for 'PT0012 - Hide Details to Show Last Date' Chris wrote: Debra, You got me excited, I actually thought that was the trick. But it is not letting me input the formula in the pivot table. Maybe I am doing something wrong. Listed below are the fields I have in the Pivot table: Client Name Column A Last Shipped Column B (ultimate goal is to show the most recent last ship date) Rep C Control # Column D File # Column E 15 50 51 Grand Total Col F Col G Col H Col I These are all fields that I placed in the pivot table. Could it be I am placing the formula in the wrong spot?? Oh Debra, I hope you come back becasue I think you might be on to something. Thanks for the help. Chris Debra Dalgleish wrote: As answered in microsoft.public.excel.misc: Maybe this will do what you want: Add a new column (LastShip) to the source data, which marks the latest shipment for each customer For example, with customers in column A, and dates in column C, enter this formula in cell D2: =IF(C2=MAX(IF($A$2:$A$200=A2,$C$2:$C$200,"")),"x", "") Substitute your last row for the 200 in the formula. This is an array formula. After you type it, hold the Ctrl and Shift keys, and press Enter. Copy the formula down to the last row of data. Refresh the pivot table, and add the LastShip field to the row area. From that field's dropdown list, leave a check mark only in the X item. Hide Detail for only that field. Does that give you the layout that you need? Chris wrote: I used to have a full head of hair until I made my pivot table!! I am trying to do the following: Client Name, Last Shipped,Rep,Control #,File #,Grand Total My list of clients shows all the detail but then when I hide the detail, the: Last Shipped Rep Control # File # are completely blank. I still have a grand total of the detail, which is great but I am trying to Show the most recent Last Last Shipped,Rep,Control #,File #, along with the Grand Total. I am just looking to just show 1 line. This really nice lady Deb tried to help me but I am completly clueless to what she wanted me to do. Tried outlines but when I refresh the data, everything goes out of wack. I am losing hope and cannot find a solution to this. Please help!!! Chris -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am stupid and don't know what the heck I am doing wrong on this pivot table!!!
Oh Debra,
You did a great job on your pivot table but I think there is some confusion. Where you have everyone listed Jones, Howard, then Jones again and all the data information. I like Jones, Howard, Gil, and within these names are the itemized details that go with that person. It is like a master list with the sub information underneath? Oh man I am prob not making much sense here I want to just show one line of recent information on the detail for one customer. If that makes sense???? I really appreciate your help but I really think this is a Microsoft glitch. Chris Debra Dalgleish wrote: The formula goes in the source table, not the pivot table. I've added a sample file to my web site: http://www.contextures.com/excelfiles.html Under PivotTables, look for 'PT0012 - Hide Details to Show Last Date' Chris wrote: Debra, You got me excited, I actually thought that was the trick. But it is not letting me input the formula in the pivot table. Maybe I am doing something wrong. Listed below are the fields I have in the Pivot table: Client Name Column A Last Shipped Column B (ultimate goal is to show the most recent last ship date) Rep C Control # Column D File # Column E 15 50 51 Grand Total Col F Col G Col H Col I These are all fields that I placed in the pivot table. Could it be I am placing the formula in the wrong spot?? Oh Debra, I hope you come back becasue I think you might be on to something. Thanks for the help. Chris Debra Dalgleish wrote: As answered in microsoft.public.excel.misc: Maybe this will do what you want: Add a new column (LastShip) to the source data, which marks the latest shipment for each customer For example, with customers in column A, and dates in column C, enter this formula in cell D2: =IF(C2=MAX(IF($A$2:$A$200=A2,$C$2:$C$200,"")),"x", "") Substitute your last row for the 200 in the formula. This is an array formula. After you type it, hold the Ctrl and Shift keys, and press Enter. Copy the formula down to the last row of data. Refresh the pivot table, and add the LastShip field to the row area. From that field's dropdown list, leave a check mark only in the X item. Hide Detail for only that field. Does that give you the layout that you need? Chris wrote: I used to have a full head of hair until I made my pivot table!! I am trying to do the following: Client Name, Last Shipped,Rep,Control #,File #,Grand Total My list of clients shows all the detail but then when I hide the detail, the: Last Shipped Rep Control # File # are completely blank. I still have a grand total of the detail, which is great but I am trying to Show the most recent Last Last Shipped,Rep,Control #,File #, along with the Grand Total. I am just looking to just show 1 line. This really nice lady Deb tried to help me but I am completly clueless to what she wanted me to do. Tried outlines but when I refresh the data, everything goes out of wack. I am losing hope and cannot find a solution to this. Please help!!! Chris -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am stupid and don't know what the heck I am doing wrong onthis pivot table!!!
Are you talking about the pivot table that starts in column K?
It has just one line per customer. Chris wrote: Oh Debra, You did a great job on your pivot table but I think there is some confusion. Where you have everyone listed Jones, Howard, then Jones again and all the data information. I like Jones, Howard, Gil, and within these names are the itemized details that go with that person. It is like a master list with the sub information underneath? Oh man I am prob not making much sense here I want to just show one line of recent information on the detail for one customer. If that makes sense???? I really appreciate your help but I really think this is a Microsoft glitch. Chris Debra Dalgleish wrote: The formula goes in the source table, not the pivot table. I've added a sample file to my web site: http://www.contextures.com/excelfiles.html Under PivotTables, look for 'PT0012 - Hide Details to Show Last Date' Chris wrote: Debra, You got me excited, I actually thought that was the trick. But it is not letting me input the formula in the pivot table. Maybe I am doing something wrong. Listed below are the fields I have in the Pivot table: Client Name Column A Last Shipped Column B (ultimate goal is to show the most recent last ship date) Rep C Control # Column D File # Column E 15 50 51 Grand Total Col F Col G Col H Col I These are all fields that I placed in the pivot table. Could it be I am placing the formula in the wrong spot?? Oh Debra, I hope you come back becasue I think you might be on to something. Thanks for the help. Chris Debra Dalgleish wrote: As answered in microsoft.public.excel.misc: Maybe this will do what you want: Add a new column (LastShip) to the source data, which marks the latest shipment for each customer For example, with customers in column A, and dates in column C, enter this formula in cell D2: =IF(C2=MAX(IF($A$2:$A$200=A2,$C$2:$C$200,"")),"x", "") Substitute your last row for the 200 in the formula. This is an array formula. After you type it, hold the Ctrl and Shift keys, and press Enter. Copy the formula down to the last row of data. Refresh the pivot table, and add the LastShip field to the row area. From that field's dropdown list, leave a check mark only in the X item. Hide Detail for only that field. Does that give you the layout that you need? Chris wrote: I used to have a full head of hair until I made my pivot table!! I am trying to do the following: Client Name, Last Shipped,Rep,Control #,File #,Grand Total My list of clients shows all the detail but then when I hide the detail, the: Last Shipped Rep Control # File # are completely blank. I still have a grand total of the detail, which is great but I am trying to Show the most recent Last Last Shipped,Rep,Control #,File #, along with the Grand Total. I am just looking to just show 1 line. This really nice lady Deb tried to help me but I am completly clueless to what she wanted me to do. Tried outlines but when I refresh the data, everything goes out of wack. I am losing hope and cannot find a solution to this. Please help!!! Chris -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am stupid and don't know what the heck I am doing wrong on this pivot table!!!
I e-mailed you your spreadsheet with the similar changes to what I
have. Its a bit barbaric but the concept I am looking for is to get that 7/7 date showing for ANderson only. Chris Debra Dalgleish wrote: Are you talking about the pivot table that starts in column K? It has just one line per customer. Chris wrote: Oh Debra, You did a great job on your pivot table but I think there is some confusion. Where you have everyone listed Jones, Howard, then Jones again and all the data information. I like Jones, Howard, Gil, and within these names are the itemized details that go with that person. It is like a master list with the sub information underneath? Oh man I am prob not making much sense here I want to just show one line of recent information on the detail for one customer. If that makes sense???? I really appreciate your help but I really think this is a Microsoft glitch. Chris Debra Dalgleish wrote: The formula goes in the source table, not the pivot table. I've added a sample file to my web site: http://www.contextures.com/excelfiles.html Under PivotTables, look for 'PT0012 - Hide Details to Show Last Date' Chris wrote: Debra, You got me excited, I actually thought that was the trick. But it is not letting me input the formula in the pivot table. Maybe I am doing something wrong. Listed below are the fields I have in the Pivot table: Client Name Column A Last Shipped Column B (ultimate goal is to show the most recent last ship date) Rep C Control # Column D File # Column E 15 50 51 Grand Total Col F Col G Col H Col I These are all fields that I placed in the pivot table. Could it be I am placing the formula in the wrong spot?? Oh Debra, I hope you come back becasue I think you might be on to something. Thanks for the help. Chris Debra Dalgleish wrote: As answered in microsoft.public.excel.misc: Maybe this will do what you want: Add a new column (LastShip) to the source data, which marks the latest shipment for each customer For example, with customers in column A, and dates in column C, enter this formula in cell D2: =IF(C2=MAX(IF($A$2:$A$200=A2,$C$2:$C$200,"")),"x", "") Substitute your last row for the 200 in the formula. This is an array formula. After you type it, hold the Ctrl and Shift keys, and press Enter. Copy the formula down to the last row of data. Refresh the pivot table, and add the LastShip field to the row area. From that field's dropdown list, leave a check mark only in the X item. Hide Detail for only that field. Does that give you the layout that you need? Chris wrote: I used to have a full head of hair until I made my pivot table!! I am trying to do the following: Client Name, Last Shipped,Rep,Control #,File #,Grand Total My list of clients shows all the detail but then when I hide the detail, the: Last Shipped Rep Control # File # are completely blank. I still have a grand total of the detail, which is great but I am trying to Show the most recent Last Last Shipped,Rep,Control #,File #, along with the Grand Total. I am just looking to just show 1 line. This really nice lady Deb tried to help me but I am completly clueless to what she wanted me to do. Tried outlines but when I refresh the data, everything goes out of wack. I am losing hope and cannot find a solution to this. Please help!!! Chris -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am stupid and don't know what the heck I am doing wrong on this pivot table!!!
Tried to e-amil you the example to what would be easier to see but it
got rejected. Is there a better e-mail for you. Chris Chris wrote: I e-mailed you your spreadsheet with the similar changes to what I have. Its a bit barbaric but the concept I am looking for is to get that 7/7 date showing for ANderson only. Chris Debra Dalgleish wrote: Are you talking about the pivot table that starts in column K? It has just one line per customer. Chris wrote: Oh Debra, You did a great job on your pivot table but I think there is some confusion. Where you have everyone listed Jones, Howard, then Jones again and all the data information. I like Jones, Howard, Gil, and within these names are the itemized details that go with that person. It is like a master list with the sub information underneath? Oh man I am prob not making much sense here I want to just show one line of recent information on the detail for one customer. If that makes sense???? I really appreciate your help but I really think this is a Microsoft glitch. Chris Debra Dalgleish wrote: The formula goes in the source table, not the pivot table. I've added a sample file to my web site: http://www.contextures.com/excelfiles.html Under PivotTables, look for 'PT0012 - Hide Details to Show Last Date' Chris wrote: Debra, You got me excited, I actually thought that was the trick. But it is not letting me input the formula in the pivot table. Maybe I am doing something wrong. Listed below are the fields I have in the Pivot table: Client Name Column A Last Shipped Column B (ultimate goal is to show the most recent last ship date) Rep C Control # Column D File # Column E 15 50 51 Grand Total Col F Col G Col H Col I These are all fields that I placed in the pivot table. Could it be I am placing the formula in the wrong spot?? Oh Debra, I hope you come back becasue I think you might be on to something. Thanks for the help. Chris Debra Dalgleish wrote: As answered in microsoft.public.excel.misc: Maybe this will do what you want: Add a new column (LastShip) to the source data, which marks the latest shipment for each customer For example, with customers in column A, and dates in column C, enter this formula in cell D2: =IF(C2=MAX(IF($A$2:$A$200=A2,$C$2:$C$200,"")),"x", "") Substitute your last row for the 200 in the formula. This is an array formula. After you type it, hold the Ctrl and Shift keys, and press Enter. Copy the formula down to the last row of data. Refresh the pivot table, and add the LastShip field to the row area. From that field's dropdown list, leave a check mark only in the X item. Hide Detail for only that field. Does that give you the layout that you need? Chris wrote: I used to have a full head of hair until I made my pivot table!! I am trying to do the following: Client Name, Last Shipped,Rep,Control #,File #,Grand Total My list of clients shows all the detail but then when I hide the detail, the: Last Shipped Rep Control # File # are completely blank. I still have a grand total of the detail, which is great but I am trying to Show the most recent Last Last Shipped,Rep,Control #,File #, along with the Grand Total. I am just looking to just show 1 line. This really nice lady Deb tried to help me but I am completly clueless to what she wanted me to do. Tried outlines but when I refresh the data, everything goes out of wack. I am losing hope and cannot find a solution to this. Please help!!! Chris -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I am stupid and don't know what the heck I am doing wrong on this pivot table!!!
Hi Deb,
Just sent example to company e-mail. Chris Chris wrote: I e-mailed you your spreadsheet with the similar changes to what I have. Its a bit barbaric but the concept I am looking for is to get that 7/7 date showing for ANderson only. Chris Debra Dalgleish wrote: Are you talking about the pivot table that starts in column K? It has just one line per customer. Chris wrote: Oh Debra, You did a great job on your pivot table but I think there is some confusion. Where you have everyone listed Jones, Howard, then Jones again and all the data information. I like Jones, Howard, Gil, and within these names are the itemized details that go with that person. It is like a master list with the sub information underneath? Oh man I am prob not making much sense here I want to just show one line of recent information on the detail for one customer. If that makes sense???? I really appreciate your help but I really think this is a Microsoft glitch. Chris Debra Dalgleish wrote: The formula goes in the source table, not the pivot table. I've added a sample file to my web site: http://www.contextures.com/excelfiles.html Under PivotTables, look for 'PT0012 - Hide Details to Show Last Date' Chris wrote: Debra, You got me excited, I actually thought that was the trick. But it is not letting me input the formula in the pivot table. Maybe I am doing something wrong. Listed below are the fields I have in the Pivot table: Client Name Column A Last Shipped Column B (ultimate goal is to show the most recent last ship date) Rep C Control # Column D File # Column E 15 50 51 Grand Total Col F Col G Col H Col I These are all fields that I placed in the pivot table. Could it be I am placing the formula in the wrong spot?? Oh Debra, I hope you come back becasue I think you might be on to something. Thanks for the help. Chris Debra Dalgleish wrote: As answered in microsoft.public.excel.misc: Maybe this will do what you want: Add a new column (LastShip) to the source data, which marks the latest shipment for each customer For example, with customers in column A, and dates in column C, enter this formula in cell D2: =IF(C2=MAX(IF($A$2:$A$200=A2,$C$2:$C$200,"")),"x", "") Substitute your last row for the 200 in the formula. This is an array formula. After you type it, hold the Ctrl and Shift keys, and press Enter. Copy the formula down to the last row of data. Refresh the pivot table, and add the LastShip field to the row area. From that field's dropdown list, leave a check mark only in the X item. Hide Detail for only that field. Does that give you the layout that you need? Chris wrote: I used to have a full head of hair until I made my pivot table!! I am trying to do the following: Client Name, Last Shipped,Rep,Control #,File #,Grand Total My list of clients shows all the detail but then when I hide the detail, the: Last Shipped Rep Control # File # are completely blank. I still have a grand total of the detail, which is great but I am trying to Show the most recent Last Last Shipped,Rep,Control #,File #, along with the Grand Total. I am just looking to just show 1 line. This really nice lady Deb tried to help me but I am completly clueless to what she wanted me to do. Tried outlines but when I refresh the data, everything goes out of wack. I am losing hope and cannot find a solution to this. Please help!!! Chris -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|