Hi all,

I'm trying to find the maximum value of a column in a pivot table so
that each time the user pivots on a field, it will recalculate the max
value for one of the columns that I can then use in other calculated
fields. Since I'll also be pulling down other fields into the pivot
table, I can't use the MAX function for a specified column letter. Is
this even possible to do? Thanks in advance to any and all replies.

Hi Mark

Not necessarily the best way, but what I have done is use a formula like
=IF(AND(LEFT(B4)<"G",B4<""),MAX(B5:B150),"")
copied across row 2 of my worksheet to show the maximum for EACH column
in the PT, allowing for it to grow and shrink.
The "G" is just testing that it is not the Grand Total column, the rest
to make sure the column has some data.

In your case, substitute the Field name you are looking for, which will
only return a value in row 2 for the relevant column
To find the value for use in another formula, with an unknown column
where it will appear, then
=INDEX(2:2,MATCH(99999999,2:2))
Substitute for the 9's any number large enough to be beyond the range
you expect.

--
Regards

Roger Govier


"lear" wrote in message
oups.com...
Hi all,

I'm trying to find the maximum value of a column in a pivot table so
that each time the user pivots on a field, it will recalculate the max
value for one of the columns that I can then use in other calculated
fields. Since I'll also be pulling down other fields into the pivot
table, I can't use the MAX function for a specified column letter. Is
this even possible to do? Thanks in advance to any and all replies.