I need to remove labes from the formulas in a spreadsheet because I need to
put it on my iPaq and unfortunately does not support labels and names. Is
there a way to accomplish it without loosing the formulas?

Thank you

Eleazar wrote:
I need to remove labes from the formulas in a spreadsheet because I need to
put it on my iPaq and unfortunately does not support labels and names. Is
there a way to accomplish it without loosing the formulas?

Thank you

Hi there,

Try ASAP utilities:

http://www.asap-utilities.com/

I believe there's a menu option for removing all range names from your
formula.

Dom

This code is probably better though, post it onto a vba module run it
on your workbook:

Sub ClearNamedRanges()
'The following subroutine will parse through all formulas on all sheets
within the
'active workbook. It will then parse through all named ranges within
the active workbook
'as well, and if the named cell exists in the current formula, it will
replace the name
'with the address that the name refers to (w/out '$'). If the referred
to cell is on the
'same sheet as the formula, the sheet name will be stripped from the
cell reference

On Error Resume Next

Dim response
response = MsgBox("Please note that depending on the number of
formulas, this process may take a very long time." + vbCrLf + "Please
do not assume that your computer is locked up; a confirmation will be
displayed when the process is complete." + vbCrLf + "Would you like to
continue?", vbExclamation + vbYesNo)

If response = vbYes Then
Dim Sh As Worksheet
Dim Rng As Range
Dim c As Range
Dim Nm As Name, tempNm As Name
Dim Ref As String
Dim count As Integer
count = 0 'used to keep track of the total number of changes

For Each Sh In ActiveWorkbook.Worksheets
'Set Sh = ActiveWorkbook.ActiveSheet
Set Rng = Sh.Cells.SpecialCells(xlCellTypeFormulas)
If Rng.count = 1 Then
For Each c In Rng
' The following chunk of code will ensure that if
we have multiple matches, it will only replace the longest (that is,
the most complete) match
' For example, if there is the named range MyRange1
and MyRange10, we wouldn't want MyRange10 to be replaced w/ the
MyRange1 reference leaving a 0 at the end...
Set tempNm = Nothing
For Each Nm In ActiveWorkbook.Names
If InStr(1, c.Formula, Nm.Name) 0 Then 'if a
match exists
If tempNm Is Nothing Then 'if we haven't
found a previous match
Set tempNm = Nm
Else ' if we have found a previous match
If Len(tempNm.Name) < Len(Nm.Name) Then
'determine which match has the most characters and use that one
Set tempNm = Nm
End If
End If
End If
Next Nm
'Now do the actual replace:
If Not tempNm Is Nothing Then
Ref = Replace(Replace(Replace(tempNm.RefersTo,
"$", ""), "=", ""), Sh.Name & "!", "") 'setup the replace string before
replacing
c.Formula = Replace(c.Formula, tempNm.Name,
Ref) 'If it can't find the name within the formula, no replace will
happen
count = count + 1
End If
Next c
End If
Next Sh
End If

MsgBox "Process complete. A total of " & count & " named ranges
were changed", vbOKOnly + vbInformation

End Sub

I'd do this against a copy of the file...

Jim Rech posted a nice response at:
http://groups.google.com/groups?thre...%40tkmsftngp03

From: Jim Rech )
Subject: Can I "De-Name" Formula Cell References?
Newsgroups: microsoft.public.excel.misc, microsoft.public.excel
Date: 2001-02-16 13:32:51 PST

To do it to a cell or two first turn on Transition Formula Entry under
Tools, Options, Transition. Then go to the cell and press F2 and Enter.
When you turn off TFE the formula references should be de-named.

If you have a lot of cells to de-name select the range and run this macro:

Sub Dename()
Dim Cell As Range
ActiveSheet.TransitionFormEntry = True
For Each Cell In Selection.SpecialCells(xlFormulas)
Cell.Formula = Cell.Formula
Next
ActiveSheet.TransitionFormEntry = False
End Sub

--
Jim Rech
Excel MVP

====
Be aware that any reference to those names in your code will be broken.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Eleazar wrote:

I need to remove labes from the formulas in a spreadsheet because I need to
put it on my iPaq and unfortunately does not support labels and names. Is
there a way to accomplish it without loosing the formulas?

Thank you

--

Dave Peterson