# Microsoft Office Application Help - Excel Help forum > Excel General >  > [SOLVED] '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

----------


## aristotle

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

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" <aristotle@discussions.microsoft.com> wrote in message
news:13932A58-6893-47DA-9E32-16E8FCDA6685@microsoft.com...
> 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 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
> >
> >
> >

----------


## aristotle

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" <aristotle@discussions.microsoft.com> wrote in message
> news:13932A58-6893-47DA-9E32-16E8FCDA6685@microsoft.com...
> > 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 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.


"aristotle" <aristotle@discussions.microsoft.com> wrote in message
news:DED4F1C3-4274-47BD-A2B5-1D7A7F8E883F@microsoft.com...
> 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" <aristotle@discussions.microsoft.com> wrote in message
> > news:13932A58-6893-47DA-9E32-16E8FCDA6685@microsoft.com...
> > > 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 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
> > > >
> > > >
> > > >
> >
> >
> >

----------


## aristotle

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" <aristotle@discussions.microsoft.com> wrote in message
> news:DED4F1C3-4274-47BD-A2B5-1D7A7F8E883F@microsoft.com...
> > 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" <aristotle@discussions.microsoft.com> wrote in message
> > > news:13932A58-6893-47DA-9E32-16E8FCDA6685@microsoft.com...
> > > > 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 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




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

--

Dave Peterson

----------


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

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42DCDF32.A873ADF9@verizonXSPAM.net...
> 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
>
> --
>
> Dave Peterson

----------


## aristotle

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" <petersod@verizonXSPAM.net> wrote in message
> news:42DCDF32.A873ADF9@verizonXSPAM.net...
> > 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
> >
> > --
> >
> > Dave Peterson
>
>
>

----------


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

"aristotle" <aristotle@discussions.microsoft.com> wrote in message
news:20722A07-065B-4529-B612-84CB5ED0B7F0@microsoft.com...
> 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" <petersod@verizonXSPAM.net> wrote in message
> > news:42DCDF32.A873ADF9@verizonXSPAM.net...
> > > 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
> > >
> > > --
> > >
> > > Dave Peterson
> >
> >
> >

----------


## aristotle

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" <aristotle@discussions.microsoft.com> wrote in message
> news:20722A07-065B-4529-B612-84CB5ED0B7F0@microsoft.com...
> > 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" <petersod@verizonXSPAM.net> wrote in message
> > > news:42DCDF32.A873ADF9@verizonXSPAM.net...
> > > > 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
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > >
> > >
> > >
>
>
>

----------


## 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 a solution other then removing hyperlinks been found?

----------

