Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace errant formula to stop annoying message
Hi,
Every now and then my worekbook throws up a dialogue box with the following warning message: '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' Short of ploughing through every page and searching every cell containing formula, does anyone know an easier way of locating the culprit formula? Thanks and regards, Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace errant formula to stop annoying message
I would start by looking for whatever is the most likely cause of that
message. Eg, if invalid cell references are suspect, search (ie Edit Find) cells containing #REF! with "Look in: Formulas" checked. After sorting out those repeat with "Look in: Values" That error occurs when a cell ref is entirely deleted with delete rows/columns, or a sheet is deleted. Range names can be similarly affected. Regards, Peter T "Mark Stephens" wrote in message ... Hi, Every now and then my worekbook throws up a dialogue box with the following warning message: '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' Short of ploughing through every page and searching every cell containing formula, does anyone know an easier way of locating the culprit formula? Thanks and regards, Mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace errant formula to stop annoying message
Thanks Peter, unfortunately didn't come up with anything, any other ideas?
regards, Mark "Peter T" <peter_t@discussions wrote in message ... I would start by looking for whatever is the most likely cause of that message. Eg, if invalid cell references are suspect, search (ie Edit Find) cells containing #REF! with "Look in: Formulas" checked. After sorting out those repeat with "Look in: Values" That error occurs when a cell ref is entirely deleted with delete rows/columns, or a sheet is deleted. Range names can be similarly affected. Regards, Peter T "Mark Stephens" wrote in message ... Hi, Every now and then my worekbook throws up a dialogue box with the following warning message: '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' Short of ploughing through every page and searching every cell containing formula, does anyone know an easier way of locating the culprit formula? Thanks and regards, Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace errant formula to stop annoying message
So you are saying there are no #REF! errors in cell references or in Defined
Names. Any broken links perhaps. Look in Edit / Links Regards, Peter T "Mark Stephens" wrote in message ... Thanks Peter, unfortunately didn't come up with anything, any other ideas? regards, Mark "Peter T" <peter_t@discussions wrote in message ... I would start by looking for whatever is the most likely cause of that message. Eg, if invalid cell references are suspect, search (ie Edit Find) cells containing #REF! with "Look in: Formulas" checked. After sorting out those repeat with "Look in: Values" That error occurs when a cell ref is entirely deleted with delete rows/columns, or a sheet is deleted. Range names can be similarly affected. Regards, Peter T "Mark Stephens" wrote in message ... Hi, Every now and then my worekbook throws up a dialogue box with the following warning message: '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' Short of ploughing through every page and searching every cell containing formula, does anyone know an easier way of locating the culprit formula? Thanks and regards, Mark |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace errant formula to stop annoying message
Try This
Sub FindBadCells() Dim Sheet As Worksheet Dim Cell As Range For Each Sheet In ThisWorkbook.Worksheets For Each Cell In Sheet.UsedRange If IsError(Cell) Then MsgBox Sheet.Name & ", " & Cell.Address(0, 0) Next Cell Next Sheet End Sub "Mark Stephens" wrote: Hi, Every now and then my worekbook throws up a dialogue box with the following warning message: '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' Short of ploughing through every page and searching every cell containing formula, does anyone know an easier way of locating the culprit formula? Thanks and regards, Mark |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace errant formula to stop annoying message
Thanks Charlie, got them all very efficiently, thankuverymuch, kind regards,
Mark:) "Charlie" wrote in message ... Try This Sub FindBadCells() Dim Sheet As Worksheet Dim Cell As Range For Each Sheet In ThisWorkbook.Worksheets For Each Cell In Sheet.UsedRange If IsError(Cell) Then MsgBox Sheet.Name & ", " & Cell.Address(0, 0) Next Cell Next Sheet End Sub "Mark Stephens" wrote: Hi, Every now and then my worekbook throws up a dialogue box with the following warning message: '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' Short of ploughing through every page and searching every cell containing formula, does anyone know an easier way of locating the culprit formula? Thanks and regards, Mark |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace errant formula to stop annoying message
Curiosity, when you say "got them all" what sort of error cells did you find
that was triggering your message. FWIW you could use the SpecialCells to find error cells, manually or programmatically. Regards, Peter T "Mark Stephens" wrote in message ... Thanks Charlie, got them all very efficiently, thankuverymuch, kind regards, Mark:) "Charlie" wrote in message ... Try This Sub FindBadCells() Dim Sheet As Worksheet Dim Cell As Range For Each Sheet In ThisWorkbook.Worksheets For Each Cell In Sheet.UsedRange If IsError(Cell) Then MsgBox Sheet.Name & ", " & Cell.Address(0, 0) Next Cell Next Sheet End Sub "Mark Stephens" wrote: Hi, Every now and then my worekbook throws up a dialogue box with the following warning message: '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' Short of ploughing through every page and searching every cell containing formula, does anyone know an easier way of locating the culprit formula? Thanks and regards, Mark |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace errant formula to stop annoying message
Hi Charlie,
They were links to a workbook I deleted and all resided in the same kind of sheets in the same place, but because they were out on a limb (column 35 or something, I couldn't begin to locate them manually, thanks again, regards, Mark "Peter T" <peter_t@discussions wrote in message ... Curiosity, when you say "got them all" what sort of error cells did you find that was triggering your message. FWIW you could use the SpecialCells to find error cells, manually or programmatically. Regards, Peter T "Mark Stephens" wrote in message ... Thanks Charlie, got them all very efficiently, thankuverymuch, kind regards, Mark:) "Charlie" wrote in message ... Try This Sub FindBadCells() Dim Sheet As Worksheet Dim Cell As Range For Each Sheet In ThisWorkbook.Worksheets For Each Cell In Sheet.UsedRange If IsError(Cell) Then MsgBox Sheet.Name & ", " & Cell.Address(0, 0) Next Cell Next Sheet End Sub "Mark Stephens" wrote: Hi, Every now and then my worekbook throws up a dialogue box with the following warning message: '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' Short of ploughing through every page and searching every cell containing formula, does anyone know an easier way of locating the culprit formula? Thanks and regards, Mark |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace errant formula to stop annoying message
Hows this for a Microsoft answer!
SYMPTOMS When you delete an item from a table that is bound to a chart in Microsoft Office Excel 2007, you may receive the following error message: 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. After you correct the references that are not valid in the table, you may continue to receive this error message. STATUS Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. ------------------------------------------------------------------------ -------- APPLIES TO • Microsoft Office Excel 2007 Back to the top Keywords: kberrmsg kbtshoot kbchart kbprb kbexpertisebeginner KB931389 Todate they have no solution, if anyone can fix this then I would be grateful *** Sent via Developersdex http://www.developersdex.com *** |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace errant formula to stop annoying message
Sam, I have experienced this problem and have a workaround that has always got rid of the problem for me. Unfortunately it takes a bit of time. The problem occurs when you delete a chart that is linked to data in your workbook (possibly only when the data is on a different sheet from your chart). The invalid references are linked somehow to the area of the sheet that contained the deleted chart and you cannot get rid of them even if you delete the entire rows. What you need to do is this (where the shett containing the error is named 'MySheet': 1. Backup workbook. 2. Rename the sheet that is causing the problem 'MySheetOld' 3. Create a new sheet 'MySheet' in your workbook. 4. Copy the rows in 'MySheetOld' down to but not including the area that contained your deleted chart. 5. Paste into exactly the same rows in 'MySheet'. 6. Copy any rows below (but not including) the area in 'MySheetOld'. 7. Paste into exactly the same rows in 'MySheet'. 8. Change any names that point to 'MySheetOld' to point to 'MySheet' 9. On all other sheets in the workbook find and replace 'MySheetOld' in formulae with 'MySheet'. 10. Save workbook. 11. Delete sheet 'MySheetOld'. 12. Error message should no longer appear. You may have to play about with this a bit if you are not sure where tha deleted chart was. Usually you can work your way down a sheet, forcing a recalculate (grouping rows is a good and accurate way to do this - just progressively add rows to a group then close and open the group). When you get to the rows that the deleted chart was in the error message will start appearing. Steps 6 & 7 may be problematic as you may not know where the corrupted rows end. Hope this all works for you. Regards, Richard Two points to Microsoft if they read this: 1. This problem occurs in both 2003 and 2007. 2. It is high time that this problem was fixed. *** Sent via Developersdex http://www.developersdex.com *** |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace errant formula to stop annoying message
Sam,
A little more on this. If you find that you still get the error message when you have followed these steps, restore your backup and start again. Repeat all steps except step 11. In some cases, deleting the old sheet somehow passes the problem to the new sheet. Don't ask me how that works! In this case, delete all the data (use Edit/Clear/All), formatting, etc on the old sheet. Do not delete the rows. Do not delete the sheet. Then just hide the sheet and don't use it. Your old sheet contains nothing now except the unresolved problem so it is very little in the way of overhead in your workbook. Your new sheet should now work ok. Richard *** Sent via Developersdex http://www.developersdex.com *** |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace errant formula to stop annoying message
I'm seeing this problem when I run a macro which copies data from the
workbook but so far, am unable to reproduce it. I should be able to figure out which worksheet/s are affected, but how do I get it to show up again. Select the rows and copy? It's not clear from your last response. Thanks, Barb Reinhardt "richardp" wrote: Sam, I have experienced this problem and have a workaround that has always got rid of the problem for me. Unfortunately it takes a bit of time. The problem occurs when you delete a chart that is linked to data in your workbook (possibly only when the data is on a different sheet from your chart). The invalid references are linked somehow to the area of the sheet that contained the deleted chart and you cannot get rid of them even if you delete the entire rows. What you need to do is this (where the shett containing the error is named 'MySheet': 1. Backup workbook. 2. Rename the sheet that is causing the problem 'MySheetOld' 3. Create a new sheet 'MySheet' in your workbook. 4. Copy the rows in 'MySheetOld' down to but not including the area that contained your deleted chart. 5. Paste into exactly the same rows in 'MySheet'. 6. Copy any rows below (but not including) the area in 'MySheetOld'. 7. Paste into exactly the same rows in 'MySheet'. 8. Change any names that point to 'MySheetOld' to point to 'MySheet' 9. On all other sheets in the workbook find and replace 'MySheetOld' in formulae with 'MySheet'. 10. Save workbook. 11. Delete sheet 'MySheetOld'. 12. Error message should no longer appear. You may have to play about with this a bit if you are not sure where tha deleted chart was. Usually you can work your way down a sheet, forcing a recalculate (grouping rows is a good and accurate way to do this - just progressively add rows to a group then close and open the group). When you get to the rows that the deleted chart was in the error message will start appearing. Steps 6 & 7 may be problematic as you may not know where the corrupted rows end. Hope this all works for you. Regards, Richard Two points to Microsoft if they read this: 1. This problem occurs in both 2003 and 2007. 2. It is high time that this problem was fixed. *** Sent via Developersdex http://www.developersdex.com *** |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace errant formula to stop annoying message
Hello Charlie, Thanx for the macro! It worked for me too I had a huge spreadsheet and when there were no data, some cells were dividing with 0. However, after I fixed everything there is still a minor problem. I run the macro and nothing appears, which means that everything works fine. When I enter data and then delete them, the irritating message pops up again! The macro does not find any invalid cells, so I guess it's Microsoft's problem.... Anyway, thanx for the macro again! Dimitris *** Sent via Developersdex http://www.developersdex.com *** |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart bug causing "Invalid References" message
Thank you. You may laugh at the Microsoft post. but it was exactly what I needed to solve my issue.
Now I know it is a bug in 2007, I can program around it. It was killing me that when I edited a the data series & changed it to an identical value the error went away. SO now I can modify the chart to point to a range I'm not deleting. Then delete the rows the chart was using. Then point the chart back to its original position. & Bingo 1 workaround. (I'm unsure if this is fixed in Excel 2010, but as I want my app to work on 2003, 2007 & 2010 it doesn't really matter. Thanks again On Tuesday, July 08, 2008 2:06 AM Mark Stephens wrote: Hi, Every now and then my worekbook throws up a dialogue box with the following warning message: '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' Short of ploughing through every page and searching every cell containing formula, does anyone know an easier way of locating the culprit formula? Thanks and regards, Mark On Tuesday, July 08, 2008 5:19 AM Peter T wrote: I would start by looking for whatever is the most likely cause of that message. Eg, if invalid cell references are suspect, search (ie Edit Find) cells containing #REF! with "Look in: Formulas" checked. After sorting out those repeat with "Look in: Values" That error occurs when a cell ref is entirely deleted with delete rows/columns, or a sheet is deleted. Range names can be similarly affected. Regards, Peter T "Mark Stephens" wrote in message ... following On Tuesday, July 08, 2008 5:37 AM Mark Stephens wrote: Thanks Peter, unfortunately did not come up with anything, any other ideas? regards, Mark On Tuesday, July 08, 2008 6:25 AM Peter T wrote: So you are saying there are no #REF! errors in cell references or in Defined Names. Any broken links perhaps. Look in Edit / Links Regards, Peter T those and containing formula? On Tuesday, July 08, 2008 8:43 AM Charli wrote: Try This Sub FindBadCells() Dim Sheet As Worksheet Dim Cell As Range For Each Sheet In ThisWorkbook.Worksheets For Each Cell In Sheet.UsedRange If IsError(Cell) Then MsgBox Sheet.Name & ", " & Cell.Address(0, 0) Next Cell Next Sheet End Sub "Mark Stephens" wrote: On Tuesday, July 08, 2008 9:26 AM Mark Stephens wrote: Thanks Charlie, got them all very efficiently, thankuverymuch, kind regards, Mark:) On Tuesday, July 08, 2008 9:48 AM Peter T wrote: Curiosity, when you say "got them all" what sort of error cells did you find that was triggering your message. FWIW you could use the SpecialCells to find error cells, manually or programmatically. Regards, Peter T "Mark Stephens" wrote in message ... regards, and containing formula? On Tuesday, July 08, 2008 9:59 AM Mark Stephens wrote: Hi Charlie, They were links to a workbook I deleted and all resided in the same kind of sheets in the same place, but because they were out on a limb (column 35 or something, I couldn't begin to locate them manually, thanks again, regards, Mark "Peter T" <peter_t@discussions wrote in message ... On Tuesday, July 15, 2008 3:26 PM sam anco wrote: Hows this for a Microsoft answer! SYMPTOMS When you delete an item from a table that is bound to a chart in Microsoft Office Excel 2007, you may receive the following error message: 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. After you correct the references that are not valid in the table, you may continue to receive this error message. STATUS Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. ------------------------------------------------------------------------ -------- APPLIES TO ? Microsoft Office Excel 2007 Back to the top Keywords: kberrmsg kbtshoot kbchart kbprb kbexpertisebeginner KB931389 Todate they have no solution, if anyone can fix this then I would be grateful *** Sent via Developersdex http://www.developersdex.com *** On Monday, September 22, 2008 8:31 AM BarbReinhard wrote: I'm seeing this problem when I run a macro which copies data from the workbook but so far, am unable to reproduce it. I should be able to figure out which worksheet/s are affected, but how do I get it to show up again. Select the rows and copy? It's not clear from your last response. Thanks, Barb Reinhardt "richardp" wrote: Submitted via EggHeadCafe - Software Developer Portal of Choice Assemblies in Folder Debug Build Checker http://www.eggheadcafe.com/tutorials...d-checker.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop displaying a message because of a formula | Excel Worksheet Functions | |||
How Do You Stop Annoying Excel Message Boxes? | Excel Worksheet Functions | |||
excel links popup message - very annoying | Excel Discussion (Misc queries) | |||
Annoying clipboard message | Excel Programming | |||
stop annoying cell selection change | Excel Programming |