 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2007GETPIVOTDATA - 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 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  SignatureRegards Roger Govier 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 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 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" <jameel68@yahoo.com> wrote in message news:eVMjO$7bHHA.4872@TK2MSFTNGP03.phx.gbl... 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 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. SignatureRegards Roger Govier 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" <roger@technologyNOSPAM4u.co.uk> wrote in message news:eutWB%238bHHA.4888@TK2MSFTNGP06.phx.gbl... 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" <jameel68@yahoo.com> wrote in message news:eVMjO$7bHHA.4872@TK2MSFTNGP03.phx.gbl... 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 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("Total",B5))) and the whole formula therefore becomes =IF(OR(ISNUMBER(FIND("Total",A5)),ISNUMBER(FIND("Total",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) SignatureRegards Roger Govier 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. SignatureRegards Roger Govier 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" <roger@technologyNOSPAM4u.co.uk> wrote in message news:eutWB%238bHHA.4888@TK2MSFTNGP06.phx.gbl... 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 SignatureRegards Roger Govier 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.