Thread
:
Sum
View Single Post
#
6
Posted to microsoft.public.excel.misc
juanpablo
external usenet poster
Posts: 108
Sum
I see,
But My pivot table range is lets say A1:K30
and according to the formula you sent me, it doesnt work.
JP
"Sandy Mann" wrote:
Oops! Missed off the second zero. The formula should have been:
=VLOOKUP("Grand Total",A1:G19,MATCH("Juan",A2:G2,0),0)
or had I used False my faux pas would have been more noticable:
=VLOOKUP("Grand Total",A1:G19,MATCH("Juan",A2:G2,FALSE),FALSE)
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
Replace @mailinator.com with @tiscali.co.uk
"Sandy Mann" wrote in message
...
To expand on my suggestion:
Assuming that:
The Pivot table starts in A1
The *subtotal* you quote is actually the *Grand Total* for that column in
the TP
The TP column label is Juan
the VLOOKUP() formula then would be:
=VLOOKUP("Grand Total",A1:G19,MATCH("Juan",A2:G2,0))
Because you have the formula in B20 I assume that the TP will never be
larger than Row 19 and longer than Column G, adjust the rows and columns
if this is not true.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
Replace @mailinator.com with @tiscali.co.uk
"Sandy Mann" wrote in message
...
Use a VLOOKUP() or HLOOKUP() function with MATCH() finding the third
element. It will then find the criteria where ever it goes to.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
Replace @mailinator.com with @tiscali.co.uk
"juanpablo" wrote in message
...
B15 is a subtotal from several values in the pivot table.
"Bernard Liengme" wrote:
I think we need to know the criteria that makes B15 the one to use.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
"juanpablo" wrote in message
...
I have the following problem.
On top of cell B20 like in B15 there is a pivot table.
My problem is that in cell B20 I have a formula that uses the value
from
B15, but every time the pivot table changes, the value I need is no
longer
on
B15 but in B17 or B13 depending if there is more or less data in the
pivot
table.
How can I still use the value from B15 in order to keep my formula in
B20
intact?
Thanks to all.
Reply With Quote
juanpablo
View Public Profile
Find all posts by juanpablo