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)
--
Regards
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.
--
Regards
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
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
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
