'reference is not valid'
 

Hi All,

i have a workbook [xl2002, xp pro] with multiple pivot tables based on the
same data range (another sheet in the same book)... i've done this using a
dynamic named range, but whenever i open the file i get a message box saying
'reference is not valid'.

my questions a -
a) is the problem likely to be because excel is trying to show a pivot table
before its worked out where the named range is pointing to?
b) can i prevent it from happening?

i will be distributing the workbook so would prefer not to use a vba
solution (i'll just get loads of people contacting me with security
problems!)

tia,

Tim

What is the dynamic range as it appears within Names - Define? Can you raed
the dynamic range from within the worksheet with the pivot tables?

"Tim" wrote:

Hi All,

i have a workbook [xl2002, xp pro] with multiple pivot tables based on the
same data range (another sheet in the same book)... i've done this using a
dynamic named range, but whenever i open the file i get a message box saying
'reference is not valid'.

my questions a -
a) is the problem likely to be because excel is trying to show a pivot table
before its worked out where the named range is pointing to?
b) can i prevent it from happening?

i will be distributing the workbook so would prefer not to use a vba
solution (i'll just get loads of people contacting me with security
problems!)

tia,

Tim

the name refers to: -

=OFFSET('ATC Data'!$C$1,0,0,COUNTA('ATC Data'!$C:$C),23)

after i click the message box, everything continues to work normally (the
pivots all show the correct data, suggesting the range name is ok/valid).
if it were jus me using the sheet i wouldn't mind, but i know i'll be
inundated with complaints (even if i fore-warn people!)

"aristotle" wrote in message
...
What is the dynamic range as it appears within Names - Define? Can you
raed
the dynamic range from within the worksheet with the pivot tables?

"Tim" wrote:

Hi All,

i have a workbook [xl2002, xp pro] with multiple pivot tables based on
the
same data range (another sheet in the same book)... i've done this using
a
dynamic named range, but whenever i open the file i get a message box
saying
'reference is not valid'.

my questions a -
a) is the problem likely to be because excel is trying to show a pivot
table
before its worked out where the named range is pointing to?
b) can i prevent it from happening?

i will be distributing the workbook so would prefer not to use a vba
solution (i'll just get loads of people contacting me with security
problems!)

tia,

Tim

Wierd! Ok, well if you say that it is actually working properly then perhaps
you can just make it so that the message doesn't appear. I know you don't
want to use VBA but this is a small line that might do the job:
Application.DisplayAlerts = False.


"Tim" wrote:

the name refers to: -

=OFFSET('ATC Data'!$C$1,0,0,COUNTA('ATC Data'!$C:$C),23)

after i click the message box, everything continues to work normally (the
pivots all show the correct data, suggesting the range name is ok/valid).
if it were jus me using the sheet i wouldn't mind, but i know i'll be
inundated with complaints (even if i fore-warn people!)

"aristotle" wrote in message
...
What is the dynamic range as it appears within Names - Define? Can you
raed
the dynamic range from within the worksheet with the pivot tables?

"Tim" wrote:

Hi All,

i have a workbook [xl2002, xp pro] with multiple pivot tables based on
the
same data range (another sheet in the same book)... i've done this using
a
dynamic named range, but whenever i open the file i get a message box
saying
'reference is not valid'.

my questions a -
a) is the problem likely to be because excel is trying to show a pivot
table
before its worked out where the named range is pointing to?
b) can i prevent it from happening?

i will be distributing the workbook so would prefer not to use a vba
solution (i'll just get loads of people contacting me with security
problems!)

tia,

Tim

thank you for your help Aristotle... i knew about displayalerts, but it
would cause me more problems to use ANY vba! believe, i've tried in the
past... its a nightmare trying to sort out peoples security settings.


"aristotle" wrote in message
...
Wierd! Ok, well if you say that it is actually working properly then
perhaps
you can just make it so that the message doesn't appear. I know you don't
want to use VBA but this is a small line that might do the job:
Application.DisplayAlerts = False.


"Tim" wrote:

the name refers to: -

=OFFSET('ATC Data'!$C$1,0,0,COUNTA('ATC Data'!$C:$C),23)

after i click the message box, everything continues to work normally
(the
pivots all show the correct data, suggesting the range name is
ok/valid).
if it were jus me using the sheet i wouldn't mind, but i know i'll be
inundated with complaints (even if i fore-warn people!)

"aristotle" wrote in message
...
What is the dynamic range as it appears within Names - Define? Can
you
raed
the dynamic range from within the worksheet with the pivot tables?

"Tim" wrote:

Hi All,

i have a workbook [xl2002, xp pro] with multiple pivot tables based
on
the
same data range (another sheet in the same book)... i've done this
using
a
dynamic named range, but whenever i open the file i get a message
box
saying
'reference is not valid'.

my questions a -
a) is the problem likely to be because excel is trying to show a
pivot
table
before its worked out where the named range is pointing to?
b) can i prevent it from happening?

i will be distributing the workbook so would prefer not to use a vba
solution (i'll just get loads of people contacting me with security
problems!)

tia,

Tim

Ok, but I'm curious to know what has gone wrong so let us know once you've
figured out what is causing the problem... :-)

"Tim" wrote:

thank you for your help Aristotle... i knew about displayalerts, but it
would cause me more problems to use ANY vba! believe, i've tried in the
past... its a nightmare trying to sort out peoples security settings.


"aristotle" wrote in message
...
Wierd! Ok, well if you say that it is actually working properly then
perhaps
you can just make it so that the message doesn't appear. I know you don't
want to use VBA but this is a small line that might do the job:
Application.DisplayAlerts = False.


"Tim" wrote:

the name refers to: -

=OFFSET('ATC Data'!$C$1,0,0,COUNTA('ATC Data'!$C:$C),23)

after i click the message box, everything continues to work normally
(the
pivots all show the correct data, suggesting the range name is
ok/valid).
if it were jus me using the sheet i wouldn't mind, but i know i'll be
inundated with complaints (even if i fore-warn people!)

"aristotle" wrote in message
...
What is the dynamic range as it appears within Names - Define? Can
you
raed
the dynamic range from within the worksheet with the pivot tables?

"Tim" wrote:

Hi All,

i have a workbook [xl2002, xp pro] with multiple pivot tables based
on
the
same data range (another sheet in the same book)... i've done this
using
a
dynamic named range, but whenever i open the file i get a message
box
saying
'reference is not valid'.

my questions a -
a) is the problem likely to be because excel is trying to show a
pivot
table
before its worked out where the named range is pointing to?
b) can i prevent it from happening?

i will be distributing the workbook so would prefer not to use a vba
solution (i'll just get loads of people contacting me with security
problems!)

tia,

Tim

You may want to search google for that phrase.

I did and found lots of hits.

One included a response from Stephen Bullen:

The most common reason I've seen for this is if you have a chart in your
file and delete the columns/rows containg its source data. Take a look at
your charts to see if any are looking wrong.

Regards
Stephen Bullen




Tim wrote:

Hi All,

i have a workbook [xl2002, xp pro] with multiple pivot tables based on the
same data range (another sheet in the same book)... i've done this using a
dynamic named range, but whenever i open the file i get a message box saying
'reference is not valid'.

my questions a -
a) is the problem likely to be because excel is trying to show a pivot table
before its worked out where the named range is pointing to?
b) can i prevent it from happening?

i will be distributing the workbook so would prefer not to use a vba
solution (i'll just get loads of people contacting me with security
problems!)

tia,

Tim

--

Dave Peterson

Thanks Dave,

There was only one graph (generated by yet another pivot table!); however
after deleting it, saving, closing, re-opening the problem is still there.

i guess i'll just have to live with it.

Tim

"Dave Peterson" wrote in message
...
You may want to search google for that phrase.

I did and found lots of hits.

One included a response from Stephen Bullen:

The most common reason I've seen for this is if you have a chart in your
file and delete the columns/rows containg its source data. Take a look at
your charts to see if any are looking wrong.

Regards
Stephen Bullen




Tim wrote:

Hi All,

i have a workbook [xl2002, xp pro] with multiple pivot tables based on
the
same data range (another sheet in the same book)... i've done this using
a
dynamic named range, but whenever i open the file i get a message box
saying
'reference is not valid'.

my questions a -
a) is the problem likely to be because excel is trying to show a pivot
table
before its worked out where the named range is pointing to?
b) can i prevent it from happening?

i will be distributing the workbook so would prefer not to use a vba
solution (i'll just get loads of people contacting me with security
problems!)

tia,

Tim

--

Dave Peterson

I don't suppose using the List All Range Names feature within ASAP Utilities
will locate the source of the problem? It's within the Information menu.
Thinking it might return a range with a #REF! range...

http://www.asap-utilities.com/


--
We are what we repeatedly do. Excellence, therefore, is not an act, but a
habit.


"Tim" wrote:

Thanks Dave,

There was only one graph (generated by yet another pivot table!); however
after deleting it, saving, closing, re-opening the problem is still there.

i guess i'll just have to live with it.

Tim

"Dave Peterson" wrote in message
...
You may want to search google for that phrase.

I did and found lots of hits.

One included a response from Stephen Bullen:

The most common reason I've seen for this is if you have a chart in your
file and delete the columns/rows containg its source data. Take a look at
your charts to see if any are looking wrong.

Regards
Stephen Bullen




Tim wrote:

Hi All,

i have a workbook [xl2002, xp pro] with multiple pivot tables based on
the
same data range (another sheet in the same book)... i've done this using
a
dynamic named range, but whenever i open the file i get a message box
saying
'reference is not valid'.

my questions a -
a) is the problem likely to be because excel is trying to show a pivot
table
before its worked out where the named range is pointing to?
b) can i prevent it from happening?

i will be distributing the workbook so would prefer not to use a vba
solution (i'll just get loads of people contacting me with security
problems!)

tia,

Tim

--

Dave Peterson

many thanks for the ASAP link... never heard of it before (been away from
the newsgroups for a while) - looks very useful!

unfortunately in this instance it didn't help... all ranges found were
correct

"aristotle" wrote in message
...
I don't suppose using the List All Range Names feature within ASAP
Utilities
will locate the source of the problem? It's within the Information menu.
Thinking it might return a range with a #REF! range...

http://www.asap-utilities.com/


--
We are what we repeatedly do. Excellence, therefore, is not an act, but a
habit.


"Tim" wrote:

Thanks Dave,

There was only one graph (generated by yet another pivot table!);
however
after deleting it, saving, closing, re-opening the problem is still
there.

i guess i'll just have to live with it.

Tim

"Dave Peterson" wrote in message
...
You may want to search google for that phrase.

I did and found lots of hits.

One included a response from Stephen Bullen:

The most common reason I've seen for this is if you have a chart in
your
file and delete the columns/rows containg its source data. Take a
look at
your charts to see if any are looking wrong.

Regards
Stephen Bullen




Tim wrote:

Hi All,

i have a workbook [xl2002, xp pro] with multiple pivot tables based
on
the
same data range (another sheet in the same book)... i've done this
using
a
dynamic named range, but whenever i open the file i get a message
box
saying
'reference is not valid'.

my questions a -
a) is the problem likely to be because excel is trying to show a
pivot
table
before its worked out where the named range is pointing to?
b) can i prevent it from happening?

i will be distributing the workbook so would prefer not to use a vba
solution (i'll just get loads of people contacting me with security
problems!)

tia,

Tim

--

Dave Peterson

What a pity, I think it has a mind of it's own. Well good luck, hope it
turns out ok!
A
--
We are what we repeatedly do. Excellence, therefore, is not an act, but a
habit.


"Tim" wrote:

many thanks for the ASAP link... never heard of it before (been away from
the newsgroups for a while) - looks very useful!

unfortunately in this instance it didn't help... all ranges found were
correct

"aristotle" wrote in message
...
I don't suppose using the List All Range Names feature within ASAP
Utilities
will locate the source of the problem? It's within the Information menu.
Thinking it might return a range with a #REF! range...

http://www.asap-utilities.com/


--
We are what we repeatedly do. Excellence, therefore, is not an act, but a
habit.


"Tim" wrote:

Thanks Dave,

There was only one graph (generated by yet another pivot table!);
however
after deleting it, saving, closing, re-opening the problem is still
there.

i guess i'll just have to live with it.

Tim

"Dave Peterson" wrote in message
...
You may want to search google for that phrase.

I did and found lots of hits.

One included a response from Stephen Bullen:

The most common reason I've seen for this is if you have a chart in
your
file and delete the columns/rows containg its source data. Take a
look at
your charts to see if any are looking wrong.

Regards
Stephen Bullen




Tim wrote:

Hi All,

i have a workbook [xl2002, xp pro] with multiple pivot tables based
on
the
same data range (another sheet in the same book)... i've done this
using
a
dynamic named range, but whenever i open the file i get a message
box
saying
'reference is not valid'.

my questions a -
a) is the problem likely to be because excel is trying to show a
pivot
table
before its worked out where the named range is pointing to?
b) can i prevent it from happening?

i will be distributing the workbook so would prefer not to use a vba
solution (i'll just get loads of people contacting me with security
problems!)

tia,

Tim

--

Dave Peterson

'reference is not valid'
 

I had a similar problem, where certain, random cells in data copied from
another spreadsheet contained hyperlinks to their original spreadsheet.
When the original spreadsheet was deleted, I started to get the
"reference not valid" error message, when I selected one of the random
cells.

The affected cells displayed the hyperlink when I hovered the mouse
over them.

This didn't become evident until I installed the ASAP utilities
recommended by Aristotle - thank you. Using the ASAP usilities, I was
able to select the whole spreadsheet and from the web menu, select
"remove all hyperlinks in selected cells". Magically no more
"reference not valid errors"


--
ghola
------------------------------------------------------------------------
ghola's Profile: http://www.excelforum.com/member.php...o&userid=35053
View this thread: http://www.excelforum.com/showthread...hreadid=388269

'reference is not valid'
 

My boss and I just ran into the same problem this morning... Has a
solution other then removing hyperlinks been found?


--
billapepper
------------------------------------------------------------------------
billapepper's Profile: http://www.excelforum.com/member.php...o&userid=35636
View this thread: http://www.excelforum.com/showthread...hreadid=388269