| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum
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 Replace @mailinator.com with @tiscali.co.uk "juanpablo" wrote in message ... 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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
