'reference is not valid'

[Tim]

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 are: -
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

 

[Guest]

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]

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!)

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?

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 are: -
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

 

[Guest]

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.

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!)

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?

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 are: -
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

 

[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.

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.

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!)

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?

:

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 are: -
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

 

[Guest]

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...

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.

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.

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!)

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?

:

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 are: -
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]

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

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 are: -
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

 

[Tim]

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

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

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 are: -
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

 

[Guest]

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.

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

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

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 are: -
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

 

[Tim]

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

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.

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

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 are: -
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

 

[Guest]

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.

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

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.

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

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 are: -
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

 

[ghola]

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"

 

[billapepper]

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