Sum

[Guest]

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.

 

[Bernard Liengme]

I think we need to know the criteria that makes B15 the one to use.
best wishes

 

[Guest]

B15 is a subtotal from several values in the pivot table.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

I see,
But My pivot table range is lets say A1:K30
and according to the formula you sent me, it doesnt work.

JP

 

[Guest]

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]

How is you Pivot table set up? What and where are the column and rows
lables?


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk