I'm Glad that you figured it out - just ignore my other past asking how your
PT is laid out.

--
REgards,


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
...
Ahhh, now I understand, A2:G2 would be just the header range, not all the
table, now it works perfect, thank you!!

JPG

"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.