|
GETPIVOTDATA - HOW TO USE CELL REFERENCE?
Hello,
I have pivot table with inventory data, such as category, part number, quanitty 1, quantity 2, etc. Just outside the pivot table i have a column called "Quantity Counted", which will be manually entered, Next column is "Variance" What I want to add the two data fields from the pivot table "Quantity 1" & "Quantity 2" and then subtract the quantity counted in the variance column. When i drag the formula down, the values do not change. I think the trick is to replace the some of the fields values with the cell referenc, but because the pivot table is a bit hierarchial, I am not sure what values to tamper. Thanks SJ |
GETPIVOTDATA - HOW TO USE CELL REFERENCE?
Hi
When you create the first GetPIvotData function, it will probably put Absolute references around a cell reference e.g. $A$4 Remove the $ for the row, so that it is $A4 and copy down, and it will change for you OK. If the function has used a Row category label e.g. "Bolts", change that reference to the cell holding the category title e.g $A4 -- Regards Roger Govier "SSJ" wrote in message ... Hello, I have pivot table with inventory data, such as category, part number, quanitty 1, quantity 2, etc. Just outside the pivot table i have a column called "Quantity Counted", which will be manually entered, Next column is "Variance" What I want to add the two data fields from the pivot table "Quantity 1" & "Quantity 2" and then subtract the quantity counted in the variance column. When i drag the formula down, the values do not change. I think the trick is to replace the some of the fields values with the cell referenc, but because the pivot table is a bit hierarchial, I am not sure what values to tamper. Thanks SJ |
GETPIVOTDATA - HOW TO USE CELL REFERENCE?
Roger,
Thanks for your response. I am sorry I am not clear as to how to make the changes: Following is the layout of the pivot table: 1) I have put the table column heading such as A, B,C, etc for idenfication 2) I have 3 different field headings: CAT(Category), LOC(Location), & PART (Part#) 3) For the sake of simplification, all i want to do is get the the data in column next to column H 4) The formula created in cell I7 is as follows: =GETPIVOTDATA("Sum of Q @ A",$A$5,"CAT","CC","PART","2192-30-111106 ","LOC","25C ") I think due to 3 different fields the cell reference is not working. If i take away the 2 fields, i.e, CAT & LOC from the pivot table and only PART remains then the change you suggested works Regards SJ A B C D E F G H Data CAT LOC PART Sum of Q @ E Sum of Q @ A Sum of UC @ E Sum of UC @ A Sum of EXTENSION CC 25C 2192-30-111106 0 1 0.00 43,361.44 43,361.44 33E3 1152106-3 0 2 0.00 29,574.28 59,148.56 35B 1152108-6 0 2 0.00 12,875.01 25,750.02 CC Total 0 5 0.00 85,810.73 128,260.02 CW 35A 2017-31-211 0 4 0.00 48,490.88 193,963.52 CW Total 0 4 0.00 48,490.88 193,963.52 FI 31C 12-10-4 0 4 0.00 6,969.05 27,876.18 C46431-3 0 1 0.00 6,293.26 6,293.26 33D 822-1071-003 0 2 0.00 9,413.95 18,827.90 35A 965-1206-011 0 1 0.00 31,762.27 31,762.27 36E 400-1409-03-L 0 1 0.00 12,270.50 12,270.50 7 TOP 8MK1524-003 0 1 0.00 9,113.85 9,113.85 HGR F 873901-401 0 9 0.00 25,723.34 231,510.06 HGR.FLOOR 4992-100-V1 0 1 0.00 24,732.19 24,732.19 S/ROOM B 9803100-501 0 1 0.00 22,675.00 22,675.00 STR B 179500-03 0 3 0.00 9,634.53 28,903.59 FI Total 0 24 0.00 158,587.94 413,964.80 "Roger Govier" wrote in message ... Hi When you create the first GetPIvotData function, it will probably put Absolute references around a cell reference e.g. $A$4 Remove the $ for the row, so that it is $A4 and copy down, and it will change for you OK. If the function has used a Row category label e.g. "Bolts", change that reference to the cell holding the category title e.g $A4 -- Regards Roger Govier "SSJ" wrote in message ... Hello, I have pivot table with inventory data, such as category, part number, quanitty 1, quantity 2, etc. Just outside the pivot table i have a column called "Quantity Counted", which will be manually entered, Next column is "Variance" What I want to add the two data fields from the pivot table "Quantity 1" & "Quantity 2" and then subtract the quantity counted in the variance column. When i drag the formula down, the values do not change. I think the trick is to replace the some of the fields values with the cell referenc, but because the pivot table is a bit hierarchial, I am not sure what values to tamper. Thanks SJ |
GETPIVOTDATA - HOW TO USE CELL REFERENCE?
Hi
If you want to mail me a copy of your workbook, I will take a look. To send direct, remove NOSPAM from my mail address. -- Regards Roger Govier "SSJ" wrote in message ... Roger, Thanks for your response. I am sorry I am not clear as to how to make the changes: Following is the layout of the pivot table: 1) I have put the table column heading such as A, B,C, etc for idenfication 2) I have 3 different field headings: CAT(Category), LOC(Location), & PART (Part#) 3) For the sake of simplification, all i want to do is get the the data in column next to column H 4) The formula created in cell I7 is as follows: =GETPIVOTDATA("Sum of Q @ A",$A$5,"CAT","CC","PART","2192-30-111106 ","LOC","25C ") I think due to 3 different fields the cell reference is not working. If i take away the 2 fields, i.e, CAT & LOC from the pivot table and only PART remains then the change you suggested works Regards SJ A B C D E F G H Data CAT LOC PART Sum of Q @ E Sum of Q @ A Sum of UC @ E Sum of UC @ A Sum of EXTENSION CC 25C 2192-30-111106 0 1 0.00 43,361.44 43,361.44 33E3 1152106-3 0 2 0.00 29,574.28 59,148.56 35B 1152108-6 0 2 0.00 12,875.01 25,750.02 CC Total 0 5 0.00 85,810.73 128,260.02 CW 35A 2017-31-211 0 4 0.00 48,490.88 193,963.52 CW Total 0 4 0.00 48,490.88 193,963.52 FI 31C 12-10-4 0 4 0.00 6,969.05 27,876.18 C46431-3 0 1 0.00 6,293.26 6,293.26 33D 822-1071-003 0 2 0.00 9,413.95 18,827.90 35A 965-1206-011 0 1 0.00 31,762.27 31,762.27 36E 400-1409-03-L 0 1 0.00 12,270.50 12,270.50 7 TOP 8MK1524-003 0 1 0.00 9,113.85 9,113.85 HGR F 873901-401 0 9 0.00 25,723.34 231,510.06 HGR.FLOOR 4992-100-V1 0 1 0.00 24,732.19 24,732.19 S/ROOM B 9803100-501 0 1 0.00 22,675.00 22,675.00 STR B 179500-03 0 3 0.00 9,634.53 28,903.59 FI Total 0 24 0.00 158,587.94 413,964.80 "Roger Govier" wrote in message ... Hi When you create the first GetPIvotData function, it will probably put Absolute references around a cell reference e.g. $A$4 Remove the $ for the row, so that it is $A4 and copy down, and it will change for you OK. If the function has used a Row category label e.g. "Bolts", change that reference to the cell holding the category title e.g $A4 -- Regards Roger Govier "SSJ" wrote in message ... Hello, I have pivot table with inventory data, such as category, part number, quanitty 1, quantity 2, etc. Just outside the pivot table i have a column called "Quantity Counted", which will be manually entered, Next column is "Variance" What I want to add the two data fields from the pivot table "Quantity 1" & "Quantity 2" and then subtract the quantity counted in the variance column. When i drag the formula down, the values do not change. I think the trick is to replace the some of the fields values with the cell referenc, but because the pivot table is a bit hierarchial, I am not sure what values to tamper. Thanks SJ |
GETPIVOTDATA - HOW TO USE CELL REFERENCE?
Hi
Book received and returned with amended formulae Original formula was =GETPIVOTDATA("Sum of Q @ E",$A$3,"CAT","CW","PART","1500-01-13 ","LOC","31D ")+ GETPIVOTDATA("Sum of Q @ A",$A$3,"CAT","CW","PART","1500-01-13 ","LOC","31D ")+ GETPIVOTDATA("Sum of UC @ E",$A$3,"CAT","CW","PART","1500-01-13 ","LOC","31D ")-I5 The column that will always have data on each line is column B, so we only need to reference that in the GetPivotData function. We don't want to use "LOC","31D " as that would be looking for 31D on each row, and because if wouldn't find it on every row, it would give a result of 0. You want the value to alter, according to what is in column B, so we give the reference B5, which will change to B6, B7 as you go down the page. GetPivotData doesn't like a cell reference on it's own however, so you have to append a null String to it by using the concatenation B5 &"" (The null can also be pre-pended to the cell reference). Because you are subtotalling, then we need to stop the calculation if the word Total appears either in column A or column B with IF(OR(ISNUMBER(FIND("Total",A5)),ISNUMBER(FIND("To tal",B5))) and the whole formula therefore becomes =IF(OR(ISNUMBER(FIND("Total",A5)),ISNUMBER(FIND("T otal",B5))),"", GETPIVOTDATA("Sum of Q @ E",$A$3,"LOC",B5 & "")+ GETPIVOTDATA("Sum of Q @ A",$A$3,"LOC",B5 & "")+ GETPIVOTDATA("Sum of UC @ E",$A$3,"LOC",B5 & "") -I5) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi If you want to mail me a copy of your workbook, I will take a look. To send direct, remove NOSPAM from my mail address. -- Regards Roger Govier "SSJ" wrote in message ... Roger, Thanks for your response. I am sorry I am not clear as to how to make the changes: Following is the layout of the pivot table: 1) I have put the table column heading such as A, B,C, etc for idenfication 2) I have 3 different field headings: CAT(Category), LOC(Location), & PART (Part#) 3) For the sake of simplification, all i want to do is get the the data in column next to column H 4) The formula created in cell I7 is as follows: =GETPIVOTDATA("Sum of Q @ A",$A$5,"CAT","CC","PART","2192-30-111106 ","LOC","25C ") I think due to 3 different fields the cell reference is not working. If i take away the 2 fields, i.e, CAT & LOC from the pivot table and only PART remains then the change you suggested works Regards SJ ABCDEFGH Data CATLOCPARTSum of Q @ ESum of Q @ ASum of UC @ ESum of UC @ ASum of EXTENSION CC25C 2192-30-111106 010.00 43,361.44 43,361.44 33E3 1152106-3 020.00 29,574.28 59,148.56 35B 1152108-6 020.00 12,875.01 25,750.02 CC Total 050.00 85,810.73 128,260.02 CW35A 2017-31-211 040.00 48,490.88 193,963.52 CW Total 040.00 48,490.88 193,963.52 FI31C 12-10-4 040.00 6,969.05 27,876.18 C46431-3 010.00 6,293.26 6,293.26 33D 822-1071-003 020.00 9,413.95 18,827.90 35A 965-1206-011 010.00 31,762.27 31,762.27 36E 400-1409-03-L 010.00 12,270.50 12,270.50 7 TOP 8MK1524-003 010.00 9,113.85 9,113.85 HGR F 873901-401 090.00 25,723.34 231,510.06 HGR.FLOOR 4992-100-V1 010.00 24,732.19 24,732.19 S/ROOM B 9803100-501 010.00 22,675.00 22,675.00 STR B 179500-03 030.00 9,634.53 28,903.59 FI Total 0240.00 158,587.94 413,964.80 "Roger Govier" wrote in message ... Hi When you create the first GetPIvotData function, it will probably put Absolute references around a cell reference e.g. $A$4 Remove the $ for the row, so that it is $A4 and copy down, and it will change for you OK. If the function has used a Row category label e.g. "Bolts", change that reference to the cell holding the category title e.g $A4 -- Regards Roger Govier "SSJ" wrote in message ... Hello, I have pivot table with inventory data, such as category, part number, quanitty 1, quantity 2, etc. Just outside the pivot table i have a column called "Quantity Counted", which will be manually entered, Next column is "Variance" What I want to add the two data fields from the pivot table "Quantity 1" & "Quantity 2" and then subtract the quantity counted in the variance column. When i drag the formula down, the values do not change. I think the trick is to replace the some of the fields values with the cell referenc, but because the pivot table is a bit hierarchial, I am not sure what values to tamper. Thanks SJ |
GETPIVOTDATA - HOW TO USE CELL REFERENCE?
Works perfectly!
Thanks SJ "Roger Govier" wrote in message ... Hi Book received and returned with amended formulae Original formula was =GETPIVOTDATA("Sum of Q @ E",$A$3,"CAT","CW","PART","1500-01-13 ","LOC","31D ")+ GETPIVOTDATA("Sum of Q @ A",$A$3,"CAT","CW","PART","1500-01-13 ","LOC","31D ")+ GETPIVOTDATA("Sum of UC @ E",$A$3,"CAT","CW","PART","1500-01-13 ","LOC","31D ")-I5 The column that will always have data on each line is column B, so we only need to reference that in the GetPivotData function. We don't want to use "LOC","31D " as that would be looking for 31D on each row, and because if wouldn't find it on every row, it would give a result of 0. You want the value to alter, according to what is in column B, so we give the reference B5, which will change to B6, B7 as you go down the page. GetPivotData doesn't like a cell reference on it's own however, so you have to append a null String to it by using the concatenation B5 &"" (The null can also be pre-pended to the cell reference). Because you are subtotalling, then we need to stop the calculation if the word Total appears either in column A or column B with IF(OR(ISNUMBER(FIND("Total",A5)),ISNUMBER(FIND("To tal",B5))) and the whole formula therefore becomes =IF(OR(ISNUMBER(FIND("Total",A5)),ISNUMBER(FIND("T otal",B5))),"", GETPIVOTDATA("Sum of Q @ E",$A$3,"LOC",B5 & "")+ GETPIVOTDATA("Sum of Q @ A",$A$3,"LOC",B5 & "")+ GETPIVOTDATA("Sum of UC @ E",$A$3,"LOC",B5 & "") -I5) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi If you want to mail me a copy of your workbook, I will take a look. To send direct, remove NOSPAM from my mail address. -- Regards Roger Govier "SSJ" wrote in message ... Roger, Thanks for your response. I am sorry I am not clear as to how to make the changes: Following is the layout of the pivot table: 1) I have put the table column heading such as A, B,C, etc for idenfication 2) I have 3 different field headings: CAT(Category), LOC(Location), & PART (Part#) 3) For the sake of simplification, all i want to do is get the the data in column next to column H 4) The formula created in cell I7 is as follows: =GETPIVOTDATA("Sum of Q @ A",$A$5,"CAT","CC","PART","2192-30-111106 ","LOC","25C ") I think due to 3 different fields the cell reference is not working. If i take away the 2 fields, i.e, CAT & LOC from the pivot table and only PART remains then the change you suggested works Regards SJ ABCDEFGH Data CATLOCPARTSum of Q @ ESum of Q @ ASum of UC @ ESum of UC @ ASum of EXTENSION CC25C 2192-30-111106 010.00 43,361.44 43,361.44 33E3 1152106-3 020.00 29,574.28 59,148.56 35B 1152108-6 020.00 12,875.01 25,750.02 CC Total 050.00 85,810.73 128,260.02 CW35A 2017-31-211 040.00 48,490.88 193,963.52 CW Total 040.00 48,490.88 193,963.52 FI31C 12-10-4 040.00 6,969.05 27,876.18 C46431-3 010.00 6,293.26 6,293.26 33D 822-1071-003 020.00 9,413.95 18,827.90 35A 965-1206-011 010.00 31,762.27 31,762.27 36E 400-1409-03-L 010.00 12,270.50 12,270.50 7 TOP 8MK1524-003 010.00 9,113.85 9,113.85 HGR F 873901-401 090.00 25,723.34 231,510.06 HGR.FLOOR 4992-100-V1 010.00 24,732.19 24,732.19 S/ROOM B 9803100-501 010.00 22,675.00 22,675.00 STR B 179500-03 030.00 9,634.53 28,903.59 FI Total 0240.00 158,587.94 413,964.80 "Roger Govier" wrote in message ... Hi When you create the first GetPIvotData function, it will probably put Absolute references around a cell reference e.g. $A$4 Remove the $ for the row, so that it is $A4 and copy down, and it will change for you OK. If the function has used a Row category label e.g. "Bolts", change that reference to the cell holding the category title e.g $A4 -- Regards Roger Govier "SSJ" wrote in message ... Hello, I have pivot table with inventory data, such as category, part number, quanitty 1, quantity 2, etc. Just outside the pivot table i have a column called "Quantity Counted", which will be manually entered, Next column is "Variance" What I want to add the two data fields from the pivot table "Quantity 1" & "Quantity 2" and then subtract the quantity counted in the variance column. When i drag the formula down, the values do not change. I think the trick is to replace the some of the fields values with the cell referenc, but because the pivot table is a bit hierarchial, I am not sure what values to tamper. Thanks SJ |
| All times are GMT +1. The time now is 08:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com