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.

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.

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

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

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