Thread
:
Sum
View Single Post
#
5
Posted to microsoft.public.excel.misc
Sandy Mann
external usenet poster
Posts: 2,345
Sum
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
Sandy Mann
View Public Profile
Find all posts by Sandy Mann