Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring lines in a pivot table
I have a large sheet and a pivot table to view the data.
i'd like to ignore some lines, e.g any line with an invoice number against it. this would only display the un-invoiced items on the pivot table. is this possible. Thank You in advance for any suggestions. -- Browny |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring lines in a pivot table
Browny,
Which version of excel are you using? If I remember correctly it isn't possible to have blank cells in the data range for pivot tables, so can i assume that if a line doesn't have an invoice number then there is something else in the cell (a zero or a phrase, such as 'not allocated')? The other alternative is that rows without an invoice number are not in your data range for the pivot table. If that is the case, could I ask if it is possible for you to put an entry into the blank cells. Personally i usually use 'NA'. (can do this using auto filter, choose blanks and then update the fields) - If you can make the cell have something consistent in it, then all you have to do is include the invoice column in your data range. For this description I will assume you used NA. - You can then drag invoice number accross as 'drop row fields here' or 'drop column fields here' (whichever works best for your layout). - Finally, click on the little arrow next to your invoice number. You will see a list of all your invoice numbers and your 'NA' value. Uncheck the NA box. Now only those entries that have an invoice number will be counted in your pivot table. There are other options, e.g, creating a copy sheet and deleting those rows without an invoice number, but that only works if you have static data. hope that makes sense, and hope it helps! M-A PS - Answer based on my use of Excel 2003. If something doesn't work could you confirm which version you are using. "Browny" wrote: I have a large sheet and a pivot table to view the data. i'd like to ignore some lines, e.g any line with an invoice number against it. this would only display the un-invoiced items on the pivot table. is this possible. Thank You in advance for any suggestions. -- Browny |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring lines in a pivot table
I'm running 2003
-- Browny "M-A" wrote: Browny, Which version of excel are you using? If I remember correctly it isn't possible to have blank cells in the data range for pivot tables, so can i assume that if a line doesn't have an invoice number then there is something else in the cell (a zero or a phrase, such as 'not allocated')? The other alternative is that rows without an invoice number are not in your data range for the pivot table. If that is the case, could I ask if it is possible for you to put an entry into the blank cells. Personally i usually use 'NA'. (can do this using auto filter, choose blanks and then update the fields) - If you can make the cell have something consistent in it, then all you have to do is include the invoice column in your data range. For this description I will assume you used NA. - You can then drag invoice number accross as 'drop row fields here' or 'drop column fields here' (whichever works best for your layout). - Finally, click on the little arrow next to your invoice number. You will see a list of all your invoice numbers and your 'NA' value. Uncheck the NA box. Now only those entries that have an invoice number will be counted in your pivot table. There are other options, e.g, creating a copy sheet and deleting those rows without an invoice number, but that only works if you have static data. hope that makes sense, and hope it helps! M-A PS - Answer based on my use of Excel 2003. If something doesn't work could you confirm which version you are using. "Browny" wrote: I have a large sheet and a pivot table to view the data. i'd like to ignore some lines, e.g any line with an invoice number against it. this would only display the un-invoiced items on the pivot table. is this possible. Thank You in advance for any suggestions. -- Browny |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring lines in a pivot table
Hello M-A
i have nothing in the NON-Invoiced cell. Only a date & amount in the invoiced cell, but i don't want to view them in my pivot table. i have to present this and we're only interested in un-invoiced for accruals. Browny "M-A" wrote: Browny, Which version of excel are you using? If I remember correctly it isn't possible to have blank cells in the data range for pivot tables, so can i assume that if a line doesn't have an invoice number then there is something else in the cell (a zero or a phrase, such as 'not allocated')? The other alternative is that rows without an invoice number are not in your data range for the pivot table. If that is the case, could I ask if it is possible for you to put an entry into the blank cells. Personally i usually use 'NA'. (can do this using auto filter, choose blanks and then update the fields) - If you can make the cell have something consistent in it, then all you have to do is include the invoice column in your data range. For this description I will assume you used NA. - You can then drag invoice number accross as 'drop row fields here' or 'drop column fields here' (whichever works best for your layout). - Finally, click on the little arrow next to your invoice number. You will see a list of all your invoice numbers and your 'NA' value. Uncheck the NA box. Now only those entries that have an invoice number will be counted in your pivot table. There are other options, e.g, creating a copy sheet and deleting those rows without an invoice number, but that only works if you have static data. hope that makes sense, and hope it helps! M-A PS - Answer based on my use of Excel 2003. If something doesn't work could you confirm which version you are using. "Browny" wrote: I have a large sheet and a pivot table to view the data. i'd like to ignore some lines, e.g any line with an invoice number against it. this would only display the un-invoiced items on the pivot table. is this possible. Thank You in advance for any suggestions. -- Browny |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring lines in a pivot table
I see, yes, sorry! I answered the wrong question.
I believe the very short answer is that at face value you cant do EXACTLY what youve asked on your current pivot table (unless one of the VBA wizs can come up with something?). However, if you are able to do an interim step or two, then you could achieve the same result in a number of ways on a new table. The solution will depend on where the original data came from, how much you can (or cant) change it, and what you need the end pivot table to look like. As I dont know those things (yet!) Ill give you a suggestion that makes the fewest changes to your original data and see how we go. Hope you dont mind but as I dont know your excel knowledge Ive tried to do this very step by step: AUTOMATICALLY CREATE A NEW SET OF SOURCE DATA You could use advance filter to create a set of source data that only has the non invoice entries. This takes two minutes once you know how, but takes ages to explain. - Create a place to hold your criteria o The Excel help file tells you to create three rows above the original source data. If you are happy to do that then please go ahead, but I dont like to mess with my original data. I usually create a new sheet called Criteria. Either way the steps after that are the same. - Now create your criteria: o In cell A1 of your new criteria sheet (or your 3 new rows) copy the title of your invoice row. It must be exactly the same as the original column title so Id suggest using copy and paste. (if you did create 3 new rows you might want to make this appear in the same column as your invoices, e.g. it could be in B1, C1, D1 etc. Doesnt matter) o Underneath this new title you want to specify blank fields so type the four characters ==. In my example his would be entered into field A2 of the criteria (but it could be in B2, C2, D2 etc as long as it is directly under the title). - Now go to, or create, a blank worksheet to hold the subset of data. Lets call it Filtered Data. - Make sure you are still looking at your blank Filtered Data sheet, then use the menu options Data/Filter/Advance Filter. Make sure you are only in a single cell and dont have a range highlighted. - You should now have the advance filter box. Choose the copy to another location. - In the first range box enter the range of your original data. Make sure that the original worksheet name is present. - In the second range box enter the range of the criteria fields. In my method this would be A1 and A2 in the criteria worksheet; in the basic excel method it would be A1 and A2 on the original sheet (or B1 and B2, C1 and C2 etc) - In the final box enter where on the Filtered data sheet you want the data to be dumped. I usually just leave it as A1. Now you have a fresh copy of the data which ONLY contains non-invoiced items and you can create your pivot table from there. ALTERNATIVES If you want to be able to show the non invoiced items in the same pivot table as your invoiced items (so you cant use the reduced data file), then you will have to do something to your source data e.g. - You could replace your blank invoices with something (e.g. NA) and then use that to filter the pivot table for entries that match that value. - If you cant change the invoice column itself then you could create an extra helper column with an IF statement (so if the invoice field is blank populate the helper column with a value, and if not blank use another value). Your pivot table should include this helper column instead of the invoice column. o An example might be entering the formula =IF(A1="","Non-Invoiced","Invoiced") o You can change the words non-invoiced and invoiced to be other things X and Y; 0 and 1; accrual entry and invoiced etc. What do you think? Do any of those work for you, or do they highlight any limitations we need to work within? M-A "Browny" wrote: Hello M-A i have nothing in the NON-Invoiced cell. Only a date & amount in the invoiced cell, but i don't want to view them in my pivot table. i have to present this and we're only interested in un-invoiced for accruals. Browny "M-A" wrote: Browny, Which version of excel are you using? If I remember correctly it isn't possible to have blank cells in the data range for pivot tables, so can i assume that if a line doesn't have an invoice number then there is something else in the cell (a zero or a phrase, such as 'not allocated')? The other alternative is that rows without an invoice number are not in your data range for the pivot table. If that is the case, could I ask if it is possible for you to put an entry into the blank cells. Personally i usually use 'NA'. (can do this using auto filter, choose blanks and then update the fields) - If you can make the cell have something consistent in it, then all you have to do is include the invoice column in your data range. For this description I will assume you used NA. - You can then drag invoice number accross as 'drop row fields here' or 'drop column fields here' (whichever works best for your layout). - Finally, click on the little arrow next to your invoice number. You will see a list of all your invoice numbers and your 'NA' value. Uncheck the NA box. Now only those entries that have an invoice number will be counted in your pivot table. There are other options, e.g, creating a copy sheet and deleting those rows without an invoice number, but that only works if you have static data. hope that makes sense, and hope it helps! M-A PS - Answer based on my use of Excel 2003. If something doesn't work could you confirm which version you are using. "Browny" wrote: I have a large sheet and a pivot table to view the data. i'd like to ignore some lines, e.g any line with an invoice number against it. this would only display the un-invoiced items on the pivot table. is this possible. Thank You in advance for any suggestions. -- Browny |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring lines in a pivot table
Thank you very much.
I'll try it today and reply -- Browny "M-A" wrote: I see, yes, sorry! I answered the wrong question. I believe the very short answer is that at face value you cant do EXACTLY what youve asked on your current pivot table (unless one of the VBA wizs can come up with something?). However, if you are able to do an interim step or two, then you could achieve the same result in a number of ways on a new table. The solution will depend on where the original data came from, how much you can (or cant) change it, and what you need the end pivot table to look like. As I dont know those things (yet!) Ill give you a suggestion that makes the fewest changes to your original data and see how we go. Hope you dont mind but as I dont know your excel knowledge Ive tried to do this very step by step: AUTOMATICALLY CREATE A NEW SET OF SOURCE DATA You could use advance filter to create a set of source data that only has the non invoice entries. This takes two minutes once you know how, but takes ages to explain. - Create a place to hold your criteria o The Excel help file tells you to create three rows above the original source data. If you are happy to do that then please go ahead, but I dont like to mess with my original data. I usually create a new sheet called Criteria. Either way the steps after that are the same. - Now create your criteria: o In cell A1 of your new criteria sheet (or your 3 new rows) copy the title of your invoice row. It must be exactly the same as the original column title so Id suggest using copy and paste. (if you did create 3 new rows you might want to make this appear in the same column as your invoices, e.g. it could be in B1, C1, D1 etc. Doesnt matter) o Underneath this new title you want to specify blank fields so type the four characters ==. In my example his would be entered into field A2 of the criteria (but it could be in B2, C2, D2 etc as long as it is directly under the title). - Now go to, or create, a blank worksheet to hold the subset of data. Lets call it Filtered Data. - Make sure you are still looking at your blank Filtered Data sheet, then use the menu options Data/Filter/Advance Filter. Make sure you are only in a single cell and dont have a range highlighted. - You should now have the advance filter box. Choose the copy to another location. - In the first range box enter the range of your original data. Make sure that the original worksheet name is present. - In the second range box enter the range of the criteria fields. In my method this would be A1 and A2 in the criteria worksheet; in the basic excel method it would be A1 and A2 on the original sheet (or B1 and B2, C1 and C2 etc) - In the final box enter where on the Filtered data sheet you want the data to be dumped. I usually just leave it as A1. Now you have a fresh copy of the data which ONLY contains non-invoiced items and you can create your pivot table from there. ALTERNATIVES If you want to be able to show the non invoiced items in the same pivot table as your invoiced items (so you cant use the reduced data file), then you will have to do something to your source data e.g. - You could replace your blank invoices with something (e.g. NA) and then use that to filter the pivot table for entries that match that value. - If you cant change the invoice column itself then you could create an extra helper column with an IF statement (so if the invoice field is blank populate the helper column with a value, and if not blank use another value). Your pivot table should include this helper column instead of the invoice column. o An example might be entering the formula =IF(A1="","Non-Invoiced","Invoiced") o You can change the words non-invoiced and invoiced to be other things X and Y; 0 and 1; accrual entry and invoiced etc. What do you think? Do any of those work for you, or do they highlight any limitations we need to work within? M-A "Browny" wrote: Hello M-A i have nothing in the NON-Invoiced cell. Only a date & amount in the invoiced cell, but i don't want to view them in my pivot table. i have to present this and we're only interested in un-invoiced for accruals. Browny "M-A" wrote: Browny, Which version of excel are you using? If I remember correctly it isn't possible to have blank cells in the data range for pivot tables, so can i assume that if a line doesn't have an invoice number then there is something else in the cell (a zero or a phrase, such as 'not allocated')? The other alternative is that rows without an invoice number are not in your data range for the pivot table. If that is the case, could I ask if it is possible for you to put an entry into the blank cells. Personally i usually use 'NA'. (can do this using auto filter, choose blanks and then update the fields) - If you can make the cell have something consistent in it, then all you have to do is include the invoice column in your data range. For this description I will assume you used NA. - You can then drag invoice number accross as 'drop row fields here' or 'drop column fields here' (whichever works best for your layout). - Finally, click on the little arrow next to your invoice number. You will see a list of all your invoice numbers and your 'NA' value. Uncheck the NA box. Now only those entries that have an invoice number will be counted in your pivot table. There are other options, e.g, creating a copy sheet and deleting those rows without an invoice number, but that only works if you have static data. hope that makes sense, and hope it helps! M-A PS - Answer based on my use of Excel 2003. If something doesn't work could you confirm which version you are using. "Browny" wrote: I have a large sheet and a pivot table to view the data. i'd like to ignore some lines, e.g any line with an invoice number against it. this would only display the un-invoiced items on the pivot table. is this possible. Thank You in advance for any suggestions. -- Browny |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring lines in a pivot table
Hello M-A
That worked. I'll need to test it a little, but it seems to be OK. Can I filter by more? My sheet is for products we hire on a daily/weekly basis and some products are 'off hired' and invoiced, others are ongoing. Can I filter to show the 'off hired' & invoiced vers the current products still on hire. My sheet has columns AB,AC,AD,etc that are reserved for new invoices. in other words if a product is still on hire then we measure the days/weeks since the last invoice and forecast how much the next invoice will be for end of month(EOM) accruals. I'd like my filter to eliminate the 'off hired' & invoiced and show the current products still hired and then projected intio the pivot table. -- Browny "M-A" wrote: I see, yes, sorry! I answered the wrong question. I believe the very short answer is that at face value you cant do EXACTLY what youve asked on your current pivot table (unless one of the VBA wizs can come up with something?). However, if you are able to do an interim step or two, then you could achieve the same result in a number of ways on a new table. The solution will depend on where the original data came from, how much you can (or cant) change it, and what you need the end pivot table to look like. As I dont know those things (yet!) Ill give you a suggestion that makes the fewest changes to your original data and see how we go. Hope you dont mind but as I dont know your excel knowledge Ive tried to do this very step by step: AUTOMATICALLY CREATE A NEW SET OF SOURCE DATA You could use advance filter to create a set of source data that only has the non invoice entries. This takes two minutes once you know how, but takes ages to explain. - Create a place to hold your criteria o The Excel help file tells you to create three rows above the original source data. If you are happy to do that then please go ahead, but I dont like to mess with my original data. I usually create a new sheet called Criteria. Either way the steps after that are the same. - Now create your criteria: o In cell A1 of your new criteria sheet (or your 3 new rows) copy the title of your invoice row. It must be exactly the same as the original column title so Id suggest using copy and paste. (if you did create 3 new rows you might want to make this appear in the same column as your invoices, e.g. it could be in B1, C1, D1 etc. Doesnt matter) o Underneath this new title you want to specify blank fields so type the four characters ==. In my example his would be entered into field A2 of the criteria (but it could be in B2, C2, D2 etc as long as it is directly under the title). - Now go to, or create, a blank worksheet to hold the subset of data. Lets call it Filtered Data. - Make sure you are still looking at your blank Filtered Data sheet, then use the menu options Data/Filter/Advance Filter. Make sure you are only in a single cell and dont have a range highlighted. - You should now have the advance filter box. Choose the copy to another location. - In the first range box enter the range of your original data. Make sure that the original worksheet name is present. - In the second range box enter the range of the criteria fields. In my method this would be A1 and A2 in the criteria worksheet; in the basic excel method it would be A1 and A2 on the original sheet (or B1 and B2, C1 and C2 etc) - In the final box enter where on the Filtered data sheet you want the data to be dumped. I usually just leave it as A1. Now you have a fresh copy of the data which ONLY contains non-invoiced items and you can create your pivot table from there. ALTERNATIVES If you want to be able to show the non invoiced items in the same pivot table as your invoiced items (so you cant use the reduced data file), then you will have to do something to your source data e.g. - You could replace your blank invoices with something (e.g. NA) and then use that to filter the pivot table for entries that match that value. - If you cant change the invoice column itself then you could create an extra helper column with an IF statement (so if the invoice field is blank populate the helper column with a value, and if not blank use another value). Your pivot table should include this helper column instead of the invoice column. o An example might be entering the formula =IF(A1="","Non-Invoiced","Invoiced") o You can change the words non-invoiced and invoiced to be other things X and Y; 0 and 1; accrual entry and invoiced etc. What do you think? Do any of those work for you, or do they highlight any limitations we need to work within? M-A "Browny" wrote: Hello M-A i have nothing in the NON-Invoiced cell. Only a date & amount in the invoiced cell, but i don't want to view them in my pivot table. i have to present this and we're only interested in un-invoiced for accruals. Browny "M-A" wrote: Browny, Which version of excel are you using? If I remember correctly it isn't possible to have blank cells in the data range for pivot tables, so can i assume that if a line doesn't have an invoice number then there is something else in the cell (a zero or a phrase, such as 'not allocated')? The other alternative is that rows without an invoice number are not in your data range for the pivot table. If that is the case, could I ask if it is possible for you to put an entry into the blank cells. Personally i usually use 'NA'. (can do this using auto filter, choose blanks and then update the fields) - If you can make the cell have something consistent in it, then all you have to do is include the invoice column in your data range. For this description I will assume you used NA. - You can then drag invoice number accross as 'drop row fields here' or 'drop column fields here' (whichever works best for your layout). - Finally, click on the little arrow next to your invoice number. You will see a list of all your invoice numbers and your 'NA' value. Uncheck the NA box. Now only those entries that have an invoice number will be counted in your pivot table. There are other options, e.g, creating a copy sheet and deleting those rows without an invoice number, but that only works if you have static data. hope that makes sense, and hope it helps! M-A PS - Answer based on my use of Excel 2003. If something doesn't work could you confirm which version you are using. "Browny" wrote: I have a large sheet and a pivot table to view the data. i'd like to ignore some lines, e.g any line with an invoice number against it. this would only display the un-invoiced items on the pivot table. is this possible. Thank You in advance for any suggestions. -- Browny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter lines with Pivot table and non pivot table columns | Charts and Charting in Excel | |||
Filter lines with Pivot table and non Pivot table columns | Excel Discussion (Misc queries) | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
How can I hide zero value lines in a pivot table? | Excel Discussion (Misc queries) | |||
Ignoring Lines with Errors in Pivot Tables | Excel Worksheet Functions |