Invalid References in formula

Hi,

I got this error message when i close my workbook: "A formula in this 
worksheet contains one or more invalid references. Verify that your formulas 
contain a valid path, workbook, range name and cell reference."

The funny thing is that this error message pop out only when i save and 
close the workbook on certain worksheets. E.g. I have worksheet a, b, c and 
d. When i am either on sheet a and d, i saved the file and close the book at 
that sheet, the error message did not pop out. However when I performed 
similar actions on either of the other 2 sheets, the error message appear. 

Anyone have this problem or anyone knows what could the problem be? I am 
running on 2003. 

I followed a thread somewhere on this solution by Max and it did not work 
out (no issue with the formula and the defined name):

Press F5 > Special. In the "Go To Special" dialog, check "Formulas", then 
uncheck all indented options except Errors*, then click OK. This will select 
all formulas with errors on the sheet, if any. Either press to delete** all 
at one go, or format these cells with say, red fill, for closer individual 
inspection / fixing later. Repeat for each sheet .. 
*ie uncheck: Numbers, Text, Logicals 
**you would probably need to, for eg: re-copy the formulas down from the top 
row (assuming these are still good, of course) 

If you have defined / named ranges ..

Insert a new sheet, select A1, then click Insert > Name > Paste > Paste List
Check / note the pasted list for any defined range(s) with #REF! errors

Then click Insert > Name > Define, select these defined ranges (one at a 
time), and either click to delete (or fix as appropriate within the "Refers 
to" box)
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

0
Utf
5/7/2010 7:21:01 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
3175 Views

Similar Articles

[PageSpeed] 37

Fellow sympathizer...

I too have experienced this somewhat sporadic problem. Similar scenario, 
working with XL 2003. I get the error when I try to close with certain 
sheets visible, no error when closing on others. My workbook does contain 
some Named Ranges (dynamic), but no visible errors.

I'll follow this thread, hopefully someone else has some insight.

-- 
Best Regards,

Luke M
"ck13" <ck13@discussions.microsoft.com> wrote in message 
news:74B7F942-AB04-4C46-AC66-BACEB56D3706@microsoft.com...
> Hi,
>
> I got this error message when i close my workbook: "A formula in this
> worksheet contains one or more invalid references. Verify that your 
> formulas
> contain a valid path, workbook, range name and cell reference."
>
> The funny thing is that this error message pop out only when i save and
> close the workbook on certain worksheets. E.g. I have worksheet a, b, c 
> and
> d. When i am either on sheet a and d, i saved the file and close the book 
> at
> that sheet, the error message did not pop out. However when I performed
> similar actions on either of the other 2 sheets, the error message appear.
>
> Anyone have this problem or anyone knows what could the problem be? I am
> running on 2003.
>
> I followed a thread somewhere on this solution by Max and it did not work
> out (no issue with the formula and the defined name):
>
> Press F5 > Special. In the "Go To Special" dialog, check "Formulas", then
> uncheck all indented options except Errors*, then click OK. This will 
> select
> all formulas with errors on the sheet, if any. Either press to delete** 
> all
> at one go, or format these cells with say, red fill, for closer individual
> inspection / fixing later. Repeat for each sheet ..
> *ie uncheck: Numbers, Text, Logicals
> **you would probably need to, for eg: re-copy the formulas down from the 
> top
> row (assuming these are still good, of course)
>
> If you have defined / named ranges ..
>
> Insert a new sheet, select A1, then click Insert > Name > Paste > Paste 
> List
> Check / note the pasted list for any defined range(s) with #REF! errors
>
> Then click Insert > Name > Define, select these defined ranges (one at a
> time), and either click to delete (or fix as appropriate within the 
> "Refers
> to" box)
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> 


0
Luke
5/7/2010 12:39:21 PM
I'd use Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

And if you're working with names, I'd get this, too:
Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
NameManager.Zip from http://www.oaltd.co.uk/mvp

The findlink program does a better job of finding bad links (including
references to bad files/folders) and the name manager will make it easier to
clean things up (if you find stuff to clean up in the names).


ck13 wrote:
> 
> Hi,
> 
> I got this error message when i close my workbook: "A formula in this
> worksheet contains one or more invalid references. Verify that your formulas
> contain a valid path, workbook, range name and cell reference."
> 
> The funny thing is that this error message pop out only when i save and
> close the workbook on certain worksheets. E.g. I have worksheet a, b, c and
> d. When i am either on sheet a and d, i saved the file and close the book at
> that sheet, the error message did not pop out. However when I performed
> similar actions on either of the other 2 sheets, the error message appear.
> 
> Anyone have this problem or anyone knows what could the problem be? I am
> running on 2003.
> 
> I followed a thread somewhere on this solution by Max and it did not work
> out (no issue with the formula and the defined name):
> 
> Press F5 > Special. In the "Go To Special" dialog, check "Formulas", then
> uncheck all indented options except Errors*, then click OK. This will select
> all formulas with errors on the sheet, if any. Either press to delete** all
> at one go, or format these cells with say, red fill, for closer individual
> inspection / fixing later. Repeat for each sheet ..
> *ie uncheck: Numbers, Text, Logicals
> **you would probably need to, for eg: re-copy the formulas down from the top
> row (assuming these are still good, of course)
> 
> If you have defined / named ranges ..
> 
> Insert a new sheet, select A1, then click Insert > Name > Paste > Paste List
> Check / note the pasted list for any defined range(s) with #REF! errors
> 
> Then click Insert > Name > Define, select these defined ranges (one at a
> time), and either click to delete (or fix as appropriate within the "Refers
> to" box)
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

-- 

Dave Peterson
0
Dave
5/7/2010 1:14:24 PM
Hi Dave,

Thanks for your response. For some reason (which I do not know), the excel 
file does not show the problem after the last weekend. will try out your 
suggestion when I faced similar issue again..

"Dave Peterson" wrote:

> I'd use Bill Manville's FindLink program:
> http://www.oaltd.co.uk/MVP/Default.htm
> 
> And if you're working with names, I'd get this, too:
> Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
> NameManager.Zip from http://www.oaltd.co.uk/mvp
> 
> The findlink program does a better job of finding bad links (including
> references to bad files/folders) and the name manager will make it easier to
> clean things up (if you find stuff to clean up in the names).
> 
> 
> ck13 wrote:
> > 
> > Hi,
> > 
> > I got this error message when i close my workbook: "A formula in this
> > worksheet contains one or more invalid references. Verify that your formulas
> > contain a valid path, workbook, range name and cell reference."
> > 
> > The funny thing is that this error message pop out only when i save and
> > close the workbook on certain worksheets. E.g. I have worksheet a, b, c and
> > d. When i am either on sheet a and d, i saved the file and close the book at
> > that sheet, the error message did not pop out. However when I performed
> > similar actions on either of the other 2 sheets, the error message appear.
> > 
> > Anyone have this problem or anyone knows what could the problem be? I am
> > running on 2003.
> > 
> > I followed a thread somewhere on this solution by Max and it did not work
> > out (no issue with the formula and the defined name):
> > 
> > Press F5 > Special. In the "Go To Special" dialog, check "Formulas", then
> > uncheck all indented options except Errors*, then click OK. This will select
> > all formulas with errors on the sheet, if any. Either press to delete** all
> > at one go, or format these cells with say, red fill, for closer individual
> > inspection / fixing later. Repeat for each sheet ..
> > *ie uncheck: Numbers, Text, Logicals
> > **you would probably need to, for eg: re-copy the formulas down from the top
> > row (assuming these are still good, of course)
> > 
> > If you have defined / named ranges ..
> > 
> > Insert a new sheet, select A1, then click Insert > Name > Paste > Paste List
> > Check / note the pasted list for any defined range(s) with #REF! errors
> > 
> > Then click Insert > Name > Define, select these defined ranges (one at a
> > time), and either click to delete (or fix as appropriate within the "Refers
> > to" box)
> > --
> > Max
> > Singapore
> > http://savefile.com/projects/236895
> > xdemechanik
> > ---
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
5/10/2010 7:23:02 AM
Reply:

Similar Artilces:

formula help 03-08-10
my formula is: =IF(E2>4,B2+4,IF(E2=3.5,B2+4,IF(E2=3,B2+E2,IF(E2=2.5,B2+3,IF(E2=2,B2+E2,IF(E2=1.5,B2+2,IF(E2=1,B2+E2,IF(E2=0.5,B2+1,IF(E2=-2,B2+E2,IF(E2=-1.5,B2-2,IF(E2=-1,B2+E2,IF(E2=-0.5,B2-1,IF(E2<-2,B2-2)))))))))))) Whats wrong? Why is it not working? To explain, I'm making a score chart for a golf tournament. A2 = NAME B2 = Handicap C2 = New Score D2 = Difference (C2-B2) E2 = D2 divided by 2 (D2/2) F2 = New Handicap Bad players get a handicap, so when they play against good players they have some kind of chanve to win. The New handicap is figured like so: Handic...

all of a sudden the formulas don't work!
I'm not sure how this happened, but within one of my spreadsheets everything is dead... I was thinking some kind of arbitrary protection got invoked, but I can't find it. Basically I have two formulae, one that takes the first name from "Last, First" and puts it in the next column. The formula is there, but it just all of a sudden doesn't work. Any help would b appreciated, I'm sure this one has cropped up before, just not to me. ...this is Excel 97. Thanks - Hugh - Hugh, it sounds like you're seeing the formulas but not the result. If this is the case, ...

Formatting in formulas not allowed before commas
If I format a formula with carriage returns and spaces for clarity, any carriage returns or spaces right before a comma are removed by Excel. For example, if I do this: (Yes, I realize this formula doesn't make sense; it's just a simple example to illustrate the problem.) =IF( OR( TRUE,TRUE ) ,TRUE,TRUE ) As soon as press Enter, Excel changes it to this: =IF( OR( TRUE,TRUE ),TRUE,TRUE ) That happens with carriage returns right before commas, spaces right before commas, and any combi...

"Login credentials supplied were invalid."
Client remotes into Corporate network using VPN with WINXP PRO from a non domain memeber machine and attempts to log into Outlook2000 client. Client logs in with current Domain credentials and receives; "Login credentials supplied were invalid." 1) Profile was recreated and same error. 2) Profile resolves to current Server and Mailbox when checking mailbox. 3) When client is logged into domain on LAN there is no problem getting into client 4) Client is able to authenticate into OWA with current domain credentials 5)REG KEY for RPC Client Protocols are all listed in Registry...

Public folder issue: Token Supplied to the function is invalid
Maybe someone will be able to help me. My configuration: MS Exchange 2003 Enterprise on Windows 2003 Server Std both in native with 2 exchange servers in my organization, both in different routing groups. On my first Exchange server I have the following problem: - I use a Full Exchange Administrator account which naturally is a local admin on the server itself - Users are able to access the public folders. - I can see the public folder instances under: System Manager, Organisation name, Administrative Groups, My Administrative group, Server, My problem server, First-Storage-Group, Public ...

formula error 12-10-09
something basic as : =(cell)*(cell) or 4,109 * 43.86. Should equate to 180,221; however displays as 180,200. formula error flag is in upper left..but I can find nothing...any suggestions? Thanks all Is this happening on more than one formula, using more than those particular cells? Perhaps try this in a different (new blank) workbook and see if it it related to just this one file. It may be a corrupt file, or maybe a formatting problem with the data. "RC" wrote: > something basic as : =(cell)*(cell) or 4,109 * 43.86. Should equate to > 180,221;...

Formula
Using Excel 2002: SP2 Greetings, I have not used Excel at any depth in for several years so I am rusty with formulas, etc. I wish to set up a formula so that When column B = "NNE" column C = "N" When column B = "ENE" column C = "NE" When column B = "NNW" column C = "N" When column B = "WNW" column C = "NW" etc Basically I want to reduce 16 compass points to 8 but the groupings may be based more on preference than what logic would suggest. I have never used a VB script and don't even know how to set up one ...

need help with formula for a newbie excel guy
this might be a stupid question to all of you excel experts out ther but please help me. cell c14 = 0 cell d14 = 5 in order to get an N/A in the equation cell, here is the formula that put it. =IF(C14=0,"N/A",D14/C14-1) is it possible to get a formula to have N/A if either cell is "0"??? please help, thank you -- ben80 ----------------------------------------------------------------------- ben803's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3513 View this thread: http://www.excelforum.com/showthread.php?threadid=54896 Instead of ...

Reference a cell in criteria range
I want to average a range if 2 criteria are met; the criteria includes cell references: C6 = 1/1/2009 (internal date) E6 = 1/1/2010 (internal date) Cells B11:BJ11 contain valid dates Cells C11:AJ11 contain valid numbers I want to average the values in C11:AJ11 that are >= C6 and <= E6. Can I reference the cells in the criteria range? I used averageifs(C11:AJ11,B11:BJ11,">=C6",B11:BJ11,"<=E6") which of course did not work. Thanks! Hi Connie You are very close: =averageifs(C11:AJ11,B11:BJ11,">=" & C6...

Is there such a formula/rule?
I have a spreadsheet with over 2,000 records (only two columns are used). Column one has a unique number. Column two has a range of numbers. For example, Column One might have the number 12345; and Column Two would have typed in it: 2004685-2004921. What this means is that in Box #12345, we have packed in that box File Numbers 2004685, 2004686, 2004687 all the way to File Number 2004921. After typing all these records, I was then told we need to have some kind of formula or rule whereby we can locate a particular file number in between that range so that we can find what box number it'...

Excel formula bar
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel The formula bar in Excel has migrated part way off the screen to the left so I can neither close it nor move it. How can I get it back onto the screen? First thing to try: While Excel is running go to System Preferences> Display & set your display resolution to a higher setting. Switch back to Excel & see if you can drag the bar back to the middle of the screen, then set your resolution back to its normal setting. If that doesn't work post a new message in the Excel group for other suggestions. Make su...

Result of formula reflected in Chart (Excel 2002)
I am conducting a program evaluation, tracking clients' progress in various Categories as a result of their participation in a program. In each Category a client may be deemed In Crisis, At Risk, Stable, Safe, or Thriving (I'll call these Series). There are Y possibilities in each Series. (Each Series might have a different value for Y.) The client responds to a questionnaire by marking responses in various components of each category. The responses are cataloged by me on a Matrix broken down by Category and Series. I want to track these responses on a chart (I'm think...

nested "if" formula problem.
Hi, group! I can't get a nested if formula to do what I want & I could use some assistance if possible. I've got numbers in 3 columns, E, F, G. I want to put a formula in column H such that it shows the difference between E and the sum of F & G (E - (F + G)). However, I want it to have a minimum (0), and a maximum (800,000). Below is what I'm trying. I get an answer of 0 in column H, despite having a value of 1,200,000 in column E and 100,000 in each of F & G - where I would expect a maximum value in D (800,000) (1,200,000 - (100K +100K) = 1,000,000, but maxes out...

Text in Formula
I have this formula: =IF(D13="","",IF(C13=D13,"",IF(C13<0,D13-C13))) The problem is that sometimes C13 and/or D13 will be text instead of a number. The text might say E or PK which is actually the same as zero. I can't figure out a way to rewrite the formula to allow for this. Try this: =IF(D13="","",IF(C13=D13,"",IF(N(C13)<0,N(D13)-N(C13)))) -- Biff Microsoft Excel MVP "JimS" <jimx22@msn.com> wrote in message news:02bcf4p6p8p7g5i0e5p69im604dqo28rqe@4ax.com... >I have this formula: > > =I...

Formula Help (or autofill)
I'm trying to figure out how to have Excel autofill something for me. .......A..........B........C.......D........E..........F.........G.........H........ 1....X..............................X................................X................ 2 3....X...........X........X 4 5 Okay, in row 1, I have some values, but there's only something in every third value. I'd like to take out some of the empty space further down the worksheet, so it's easier to see, such as in row 3. How do I have Excel autofill the entire row 3, instead of me having to type in those values manually. In...

can i rearrange following names in alphabetic order..is theer any formula
Hi friends i hope all u peple are fine.. i have following names in my excel sheet....... Hussain Zain sohail Nauman Khan Qazmi Ahmad I want these names to rearrange in alphabetic order is there any way t do this after writing these name sin excel sheet..... Its urgent.bye take car -- naughtybo ----------------------------------------------------------------------- naughtyboy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3715 View this thread: http://www.excelforum.com/showthread.php?threadid=56890 See other thread. -- HTH Bob Phillips (replace somewhe...

Maintaining Formulas
I recently downloaded a check register template. It gave me 89 lines of entry. When I go beyond that and need to add more rows, it loses the formulas. I am not proficient obviously with Excel, just want to maintain a check register on my computer. You need to copy the formulas to the rows below row 89; this should work OK. Please hit Yes if my comments have helped. Thanks. "dcontois" wrote: > I recently downloaded a check register template. It gave me 89 lines of > entry. When I go beyond that and need to add more rows, it loses the > formulas...

tool to re-reference forms after usizing?
Hi I am in the process of upsizing an access database to sql server. The reason for this post is because after upsizing i would like to rename columns, tables and queries in the sql database as originally the field names were put together with no logic at all and are an absolute mess. I understand that i would have to then change all the queries etc.. to show the new names. What i would like to aviod if possible is having to go into each form, report, marco and re-select the fields. Are there any mapping tools out there which can do this for me? dont mind buying something if there is. ...

Formula #2
I would like to have a formula that has several if statements in it and give me a dollar amount for the date and code that i specify. for example i have a spread sheet set up as follows date reason code cost 6/22/04 we $1.00 6/22/04 be $1.00 6/22/04 msg $1.00 i need it to search for a specific date then search for the 3 reason codes i have listed the give me the total dollar amount for that date and reason codes. i have been stuggling to figure out the formula that can capture this information for me. any help would be greatly appreciated. t...

Where do I ask about formulas?
I posted a few questions in the excel.worksheetfunctions group. Was that a bad idea? It doesn't seem very active. What would this be a better group to post a question about formulas? Thanks. The name was changed to excel.worksheet.functions "Bruce Norris" <bruce25norris@yahoo.com> wrote in message news:LXi4e.28034$f%4.25742@bignews1.bellsouth.net... > I posted a few questions in the excel.worksheetfunctions group. Was that a > bad idea? It doesn't seem very active. > > What would this be a better group to post a question about formulas? > > Tha...

What formula will count the number of data < 0 in a column?
I have a list with values from -50 to + 50. I need a formula that will produce the percentage of values in that column less than zero. Hi =COUNTIF(A1:A100,"<0")/COUNT(A1:A100) and format as percentage -- Regards Frank Kabel Frankfurt, Germany "JackieB" <JackieB@discussions.microsoft.com> schrieb im Newsbeitrag news:30735B3F-40E9-4782-89F9-45709A5B9BCD@microsoft.com... > I have a list with values from -50 to + 50. I need a formula that will > produce the percentage of values in that column less than zero. > > If the range is A1:A4: =COUNTIF(A1...

'' is an invalid QName
Help I'm trying to perform a transform and I keep on getting '' is invalid QName. The weird thing is, I can take the same code, stylesheet and XML, put into a Console app and it works okay. I've tested the stylesheet in Stylus Studio and tried using nxslt and both work fine. Any ideas? Glenn Glenn wrote: > I'm trying to perform a transform and I keep on getting '' is invalid QName. > The weird thing is, I can take the same code, stylesheet and XML, put into a > Console app and it works okay. I've tested the stylesheet in Stylus Studio > and ...

Formula help #51
Could someone help with a formula to show excess wages? For example, if the wage limit is $5000 and the upper limit is $4000, excess wages are $1000. I need a formula that shows only the excess amounts, not minus (-) amounts. Hi if A1=5000 B1=4000 In C1, put: =IF(A1-B1>0,A1-B1,"") HTH Michael Mitchelson "sdmccabe" wrote: > Could someone help with a formula to show excess wages? For example, if the > wage limit is $5000 and the upper limit is $4000, excess wages are $1000. I > need a formula that shows only the excess amounts, not minus (-) amount...

How do I create a formula to add 10 workdays to a specified date .
I want to add 10 workdays to a specified date. The formula I have used in the past was =WORKDAY(A2,10), but for some reason I keep getting the #NAME? error when I try to use it now. How can I eliminate or fix this error? I've tried everything! Make sure you have the analysis toolpak loaded. Tools|Addins (you may need your distribution CD if that analysis toolpak isn't installed) Dallas7905 wrote: > > I want to add 10 workdays to a specified date. The formula I have used in > the past was =WORKDAY(A2,10), but for some reason I keep getting the #NAME? > error when...

Exact reference
Hey~ I am trying to make an output sheet from a separate calculation sheet. I am pulling certain values from the calculation sheet and putting them on the output sheet. The problem is that when I add a row or cut and copy information on the calculation sheet, it doesn't change on my output sheet. If it does change it says #REF. Is there anything I can do so that I can just enter the formulas 1 time into the output sheet and not have to worry about problems when I add rows in the calculations sheet? You could give each cell on your calculation sheet that you want to transfer, a...