Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic formula for Pivot Table
Hello,
Im using Excel 2000. Im trying to name a range for a dynamic formula to be used in a pivot table. I found a very good tip on this site, but I cant get the named range to work. Here is what I've done: I name the range by choosing Insert Name Define I type my name RRBB001 without the quotes. In the Refers to box I type =offset(data!$A$1,0,0,Counta(Data!$A:$A),7) I click OK Then a choose the pivot table and for the range I type RRBB001, click Next Here is where I get the error message Reference is not valid However, when I go back into Insert Name Paste, the name prints and the offset formula. Help is appreciated as always. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic formula for Pivot Table
Hi
Did you enter =RRBB001 That is what is required in the source dialogue -- Regards Roger Govier "JHL" wrote in message ... Hello, I'm using Excel 2000. I'm trying to name a range for a dynamic formula to be used in a pivot table. I found a very good tip on this site, but I can't get the "named range" to work. Here is what I've done: I name the range by choosing Insert Name Define I type my name "RRBB001" without the quotes. In the Refers to box I type =offset(data!$A$1,0,0,Counta(Data!$A:$A),7) I click OK Then a choose the pivot table and for the range I type "RRBB001", click Next Here is where I get the error message " Reference is not valid" However, when I go back into Insert Name Paste, the name prints and the offset formula. Help is appreciated as always. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic formula for Pivot Table
Are you creating the pivot table in a different workbook?
If so, have the source data workbook open, and include the workbook name in the reference, e.g.: OtherBook.xls!RRBB001 JHL wrote: Roger I tried that after your suggestion, but it still doesn't work for me. I get the same error message. "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... Hi Did you enter =RRBB001 That is what is required in the source dialogue -- Regards Roger Govier "JHL" wrote in message ... Hello, I'm using Excel 2000. I'm trying to name a range for a dynamic formula to be used in a pivot table. I found a very good tip on this site, but I can't get the "named range" to work. Here is what I've done: I name the range by choosing Insert Name Define I type my name "RRBB001" without the quotes. In the Refers to box I type =offset(data!$A$1,0,0,Counta(Data!$A:$A),7) I click OK Then a choose the pivot table and for the range I type "RRBB001", click Next Here is where I get the error message " Reference is not valid" However, when I go back into Insert Name Paste, the name prints and the offset formula. Help is appreciated as always. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic formula for Pivot Table
Hi
For the benefit of others, could you say what you did and how you resolved the problem? -- Regards Roger Govier "JHL" wrote in message ... I've got this solved now. Thanks for the replies "Debra Dalgleish" wrote in message ... Are you creating the pivot table in a different workbook? If so, have the source data workbook open, and include the workbook name in the reference, e.g.: OtherBook.xls!RRBB001 JHL wrote: Roger I tried that after your suggestion, but it still doesn't work for me. I get the same error message. "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... Hi Did you enter =RRBB001 That is what is required in the source dialogue -- Regards Roger Govier "JHL" wrote in message ... Hello, I'm using Excel 2000. I'm trying to name a range for a dynamic formula to be used in a pivot table. I found a very good tip on this site, but I can't get the "named range" to work. Here is what I've done: I name the range by choosing Insert Name Define I type my name "RRBB001" without the quotes. In the Refers to box I type =offset(data!$A$1,0,0,Counta(Data!$A:$A),7) I click OK Then a choose the pivot table and for the range I type "RRBB001", click Next Here is where I get the error message " Reference is not valid" However, when I go back into Insert Name Paste, the name prints and the offset formula. Help is appreciated as always. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic formula for Pivot Table
I just did the same thing, so I think I know what resolved the problem. The
problem isn't with the pivot table. When defining the named range, I did a cut 'n paste from the Contextures instructions, without changing the "data!" part to the actual worksheet name. Since I didn't have a worksheet named "data", the pivot table couldn't find the named range. "Roger Govier" wrote: Hi For the benefit of others, could you say what you did and how you resolved the problem? -- Regards Roger Govier "JHL" wrote in message ... I've got this solved now. Thanks for the replies "Debra Dalgleish" wrote in message ... Are you creating the pivot table in a different workbook? If so, have the source data workbook open, and include the workbook name in the reference, e.g.: OtherBook.xls!RRBB001 JHL wrote: Roger I tried that after your suggestion, but it still doesn't work for me. I get the same error message. "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... Hi Did you enter =RRBB001 That is what is required in the source dialogue -- Regards Roger Govier "JHL" wrote in message ... Hello, I'm using Excel 2000. I'm trying to name a range for a dynamic formula to be used in a pivot table. I found a very good tip on this site, but I can't get the "named range" to work. Here is what I've done: I name the range by choosing Insert Name Define I type my name "RRBB001" without the quotes. In the Refers to box I type =offset(data!$A$1,0,0,Counta(Data!$A:$A),7) I click OK Then a choose the pivot table and for the range I type "RRBB001", click Next Here is where I get the error message " Reference is not valid" However, when I go back into Insert Name Paste, the name prints and the offset formula. Help is appreciated as always. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic range in Pivot table | Excel Discussion (Misc queries) | |||
Pivot table dynamic Filter | Excel Worksheet Functions | |||
Dynamic Range in a Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table - Dynamic Field | Excel Discussion (Misc queries) | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) |