OT - ms Excel issues
mcerd1 - 12/7/12 at 09:14 AM
MS Excel is driving me mad today 
I've got a big excel workbook that basically does 20% of my work for me, its been tweaked hundreds of times so I've been tiring to clean it
up while adding some updated sheets and nice extra functions...
has anyone had any of these issues before ?
1- Stupidly in some ancient version I've used named cells (i.e. called it 'Job_Number' instead of 'H9' ) - how can I get rid
of these ? as they now cause more problems than they ever solved 
2- I've got alot of sheets with redundant links to other workbooks, is there any way of finding the offending formulae so I can delete / edit
them ?
3- The formulae some cells for some unknown reason suddenly stop working once they have been edited (nothing wrong with the formulae itself) and they
won’t start working again until I copy and paste another cell over the top and re-write it
this can be as simple as =A-B any ideas ?
oh this has mostly been created in 2000 version, but the issues are still there in 2007 - and re-writing the whole thing from scratch is not an option
!
cheers
-Robert
Slimy38 - 12/7/12 at 09:29 AM
1. Bit of vbcode will be the quickest method I can think of;
Sub DeleteNamedRanges()
Dim namR As Name
'Remove all named ranges from the workbook
For Each namR In ActiveWorkbook.Names
If namR <> "No" Then
namR.Delete
End If
Next namR
'Message box to advise that named ranges have been removed.
MsgBox ("Clean Completed"
End Sub
2. Find functionality using the sheet name as criteria will show you the cells that link to other sheets. Find and replace can update them all in
one.
3. That one is a bit more troublesome, I've seen this before but never been able to permanently get rid of it. The best method is to copy the
contents of the worksheet to an entirely different spreadsheet, but even then it doesn't always work.
MRLuke - 12/7/12 at 09:38 AM
Think this is easier.
On 2007+
1. Go to Formulas tab, "Name Manager" Click the top one, hold shift and click the bottom one, then "delete"
2. Go to the Data tab, "Edit Links" >> "Break Link" This lets you remove the links to redundant worksheets / books.
3. For your formulas I expect the problem is that they don't calculate until you save or edit. To fix this on the Formulas tab go to
"Calculation Options" and select "automatic"
...bit of an excel nerd here 
[Edited on 12/7/12 by MRLuke]
mcerd1 - 12/7/12 at 09:46 AM
quote:
Originally posted by MRLuke
3. For your formulas I expect the problem is that they don't calculate until you save or edit. To fix this on the Formulas tab go to
"Calculation Options" and select "automatic"
its always on automatic...
I should have said it actually shows the formula as text instead of calculating it and will not calculate it untill I've deleted or pasted over
the cell and re-writen the formula ??
Slimy38 - 12/7/12 at 10:09 AM
quote:
Originally posted by mcerd1
quote:
Originally posted by MRLuke
3. For your formulas I expect the problem is that they don't calculate until you save or edit. To fix this on the Formulas tab go to
"Calculation Options" and select "automatic"
its always on automatic...
I should have said it actually shows the formula as text instead of calculating it and will not calculate it untill I've deleted or pasted over
the cell and re-writen the formula ??
Yep, that's how I've had it. Everything suggests that it should just work, but the formulae never get calculated. The only thing I found on
the internet is that if you put a ' (single quote) in front of the formula, that maintains it as text. But the formulae I work with don't
have the single quote.
If you do find a way to fix this one, let me know!
MikeRJ - 12/7/12 at 10:20 AM
quote:
Originally posted by mcerd1
quote:
Originally posted by MRLuke
3. For your formulas I expect the problem is that they don't calculate until you save or edit. To fix this on the Formulas tab go to
"Calculation Options" and select "automatic"
its always on automatic...
I should have said it actually shows the formula as text instead of calculating it and will not calculate it untill I've deleted or pasted over
the cell and re-writen the formula ??
Sounds like the cell has had a "Text" format applied to it. Right click cell, select "Format Cells", choose "Number"
tab and then select "General" as the format type.
mcerd1 - 12/7/12 at 10:45 AM
quote:
Originally posted by MikeRJ
sounds like the cell has had a "Text" format applied to it. Right click cell, select "Format Cells", choose "Number"
tab and then select "General" as the format type.
nope, thats not it
although sometimes it is a text answer to the formula.....
MRLuke - 12/7/12 at 12:53 PM
Paste the cell contents into this thread (or U2U).
Personally i've not run into the problem you describe. Im happy to have a look at it if you want to email it across but I imagine it will be
confidential or similar?
There is a format option to show formula rather than results but I think this normally applies to the whole sheet rather than selected cells.
[Edited on 12/7/12 by MRLuke]
RIE - 12/7/12 at 12:56 PM
quote:
Originally posted by mcerd1
quote:
Originally posted by MikeRJ
sounds like the cell has had a "Text" format applied to it. Right click cell, select "Format Cells", choose "Number"
tab and then select "General" as the format type.
nope, thats not it
although sometimes it is a text answer to the formula.....
I've had this before and it was due to editing a formula after the cell had been formatted as text, as MikeRJ said. The result of the formula
doesn't have an affect, just the text format applied to the cell.
2. Do Find (CTRL + F) for .xls - this should take you to any cells with a spreadsheet filename in it.
[Edited on 12/7/12 by RIE]
MRLuke - 12/7/12 at 01:25 PM
Assuming they aren't array formulas?