If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 --- |
#2
|
|||
|
|||
Invalid References in formula
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" wrote in message ... 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 --- |
#3
|
|||
|
|||
Invalid References in formula
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 |
#4
|
|||
|
|||
Invalid References in formula
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 . |
Thread Tools | |
Display Modes | |
|
|