Range names
 

Folks,

Kindly, answer whichever you can, even if you can't answer all of these:

If I have named a cell, can I later edit that name? If so, will the cells
that depend on it pick up the revised name?

If the answer to the first question is yes, is there a way to change say,
every range name, that currently starts with "project.a" and, say, replace
that beginning with "phase.1"?

Also, if I name an independent cell after another dependent cell's equation
already refers to it, how do I get the dependent cell equation to show the
range name in its equation? If I must edit the equation, is there some
shortcut that would minimize the number of keystrokes involved?

If I go to a range named cell and perform edit-clear-all, will the range
name also be eliminated?

Kindly do not recommend any outside provider add-ins, to do any of the
above, as they tend to freak my bosses out.

Thanks very much!
Dean

Range names
 

Hi Dean,

If I have named a cell, can I later edit that name?

Yes

If so, will the cells that depend on it pick up the revised name?

No, the formulae will be broken. They can, however, be repaired.

For example, if formulae exist which depend on the names commencing in
'project.a' which are changed to corresponding names commencing in
'phase.1', the resultant broken formulae could be repaired with something
like:

'==================
Public Sub RepairBrokenFormulae()
Dim rng As Range
Dim rcell As Range
Dim sStr As String

On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

For Each rcell In rng.Cells
rcell.Formula = Application.Substitute(rcell.Formula, "project.a",
"phase.1")
Next

End Sub
'<<==================

If the answer to the first question is yes, is there a way to change say,
every range name, that currently starts with "project.a" and, say,
replace that beginning with "phase.1"?

Yes, try something like:

'==================
Public Sub EditNames()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
If nm.Name Like "project.a*" Then
ActiveWorkbook.Names(nm.Name).Name = _
Application.Substitute(nm.Name, "project.a", "phase.1")
End If
Next nm

End Sub

'<<==================


If I go to a range named cell and perform edit-clear-all, will the range
name also be eliminated?

No, clearing the constituent cells of a named range does not delete the
range name.


---
Regards,
Norman



"Dean" wrote in message
...
Folks,

Kindly, answer whichever you can, even if you can't answer all of these:

If I have named a cell, can I later edit that name? If so, will the cells
that depend on it pick up the revised name?

If the answer to the first question is yes, is there a way to change say,
every range name, that currently starts with "project.a" and, say,
replace that beginning with "phase.1"?

Also, if I name an independent cell after another dependent cell's
equation already refers to it, how do I get the dependent cell equation to
show the range name in its equation? If I must edit the equation, is
there some shortcut that would minimize the number of keystrokes involved?

If I go to a range named cell and perform edit-clear-all, will the range
name also be eliminated?

Kindly do not recommend any outside provider add-ins, to do any of the
above, as they tend to freak my bosses out.

Thanks very much!
Dean

Range names
 

Hi Dean

Sub chng()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
If Left(nm.Name, 9) = "Project.a" Then
nm.Name = "phase.1" & Right(nm.Name, Len(nm.Name) - 9)
End If
Next nm
End Sub

will change all the named ranges as requested. Running this will result in
#Name? errors in any cell with a formula refering to the original Name. You
can then correct these by selecting all cells and using Find/Replace to Find
Project.a and replace with phase.1. Use the options button to Look in
Formulas.

Edit Clear All will not remove range names.

Hope this helps
Rowan

"Dean" wrote:

Folks,

Kindly, answer whichever you can, even if you can't answer all of these:

If I have named a cell, can I later edit that name? If so, will the cells
that depend on it pick up the revised name?

If the answer to the first question is yes, is there a way to change say,
every range name, that currently starts with "project.a" and, say, replace
that beginning with "phase.1"?

Also, if I name an independent cell after another dependent cell's equation
already refers to it, how do I get the dependent cell equation to show the
range name in its equation? If I must edit the equation, is there some
shortcut that would minimize the number of keystrokes involved?

If I go to a range named cell and perform edit-clear-all, will the range
name also be eliminated?

Kindly do not recommend any outside provider add-ins, to do any of the
above, as they tend to freak my bosses out.

Thanks very much!
Dean

Range names
 

Thanks to both you and Rowan - this worked VERY nicely. Now, one other
thing, if I may impose:.

All these phase.1 range names refer to a phase 1 column, which will be
copied nine times with the apt cells renamed as phase.2* thru phase.10*. I
know how to use edit replace for the formulas, but is there a way to create
the same range names in each of the nine new columns (ten times as many in
total) so that they start with phase.2 through phase.10, rather than (and in
addition to) the ones that are already named with beginning with phase.1?

Thanks again very much!
Dean

"Norman Jones" wrote in message
...
Hi Dean,

If I have named a cell, can I later edit that name?

Yes

If so, will the cells that depend on it pick up the revised name?

No, the formulae will be broken. They can, however, be repaired.

For example, if formulae exist which depend on the names commencing in
'project.a' which are changed to corresponding names commencing in
'phase.1', the resultant broken formulae could be repaired with something
like:

'==================
Public Sub RepairBrokenFormulae()
Dim rng As Range
Dim rcell As Range
Dim sStr As String

On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

For Each rcell In rng.Cells
rcell.Formula = Application.Substitute(rcell.Formula, "project.a",
"phase.1")
Next

End Sub
'<<==================

If the answer to the first question is yes, is there a way to change say,
every range name, that currently starts with "project.a" and, say,
replace that beginning with "phase.1"?

Yes, try something like:

'==================
Public Sub EditNames()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
If nm.Name Like "project.a*" Then
ActiveWorkbook.Names(nm.Name).Name = _
Application.Substitute(nm.Name, "project.a", "phase.1")
End If
Next nm

End Sub

'<<==================


If I go to a range named cell and perform edit-clear-all, will the range
name also be eliminated?

No, clearing the constituent cells of a named range does not delete the
range name.


---
Regards,
Norman



"Dean" wrote in message
...
Folks,

Kindly, answer whichever you can, even if you can't answer all of these:

If I have named a cell, can I later edit that name? If so, will the
cells that depend on it pick up the revised name?

If the answer to the first question is yes, is there a way to change say,
every range name, that currently starts with "project.a" and, say,
replace that beginning with "phase.1"?

Also, if I name an independent cell after another dependent cell's
equation already refers to it, how do I get the dependent cell equation
to show the range name in its equation? If I must edit the equation, is
there some shortcut that would minimize the number of keystrokes
involved?

If I go to a range named cell and perform edit-clear-all, will the range
name also be eliminated?

Kindly do not recommend any outside provider add-ins, to do any of the
above, as they tend to freak my bosses out.

Thanks very much!
Dean

Range names
 

Hi Dean

Copying the column will not copy the range names. You could use code to
add the range names e.g:

Dim i As Integer
For i = 2 To 10
ActiveWorkbook.Names.Add "Phase." & i & "theRest", _
ActiveSheet.Cells(1, i)
Next i

This adds names to the cell in row one in columns B to I. It shouldn't
be too difficult to adapt this to your specific needs.

Hope this helps
Rowan

Dean wrote:
Thanks to both you and Rowan - this worked VERY nicely. Now, one other
thing, if I may impose:.

All these phase.1 range names refer to a phase 1 column, which will be
copied nine times with the apt cells renamed as phase.2* thru phase.10*. I
know how to use edit replace for the formulas, but is there a way to create
the same range names in each of the nine new columns (ten times as many in
total) so that they start with phase.2 through phase.10, rather than (and in
addition to) the ones that are already named with beginning with phase.1?

Thanks again very much!
Dean

"Norman Jones" wrote in message
...
Hi Dean,

If I have named a cell, can I later edit that name?

Yes

If so, will the cells that depend on it pick up the revised name?

No, the formulae will be broken. They can, however, be repaired.

For example, if formulae exist which depend on the names commencing in
'project.a' which are changed to corresponding names commencing in
'phase.1', the resultant broken formulae could be repaired with something
like:

'==================
Public Sub RepairBrokenFormulae()
Dim rng As Range
Dim rcell As Range
Dim sStr As String

On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

For Each rcell In rng.Cells
rcell.Formula = Application.Substitute(rcell.Formula, "project.a",
"phase.1")
Next

End Sub
'<<==================

If the answer to the first question is yes, is there a way to change say,
every range name, that currently starts with "project.a" and, say,
replace that beginning with "phase.1"?

Yes, try something like:

'==================
Public Sub EditNames()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
If nm.Name Like "project.a*" Then
ActiveWorkbook.Names(nm.Name).Name = _
Application.Substitute(nm.Name, "project.a", "phase.1")
End If
Next nm

End Sub

'<<==================


If I go to a range named cell and perform edit-clear-all, will the range
name also be eliminated?

No, clearing the constituent cells of a named range does not delete the
range name.


---
Regards,
Norman



"Dean" wrote in message
...
Folks,

Kindly, answer whichever you can, even if you can't answer all of these:

If I have named a cell, can I later edit that name? If so, will the
cells that depend on it pick up the revised name?

If the answer to the first question is yes, is there a way to change say,
every range name, that currently starts with "project.a" and, say,
replace that beginning with "phase.1"?

Also, if I name an independent cell after another dependent cell's
equation already refers to it, how do I get the dependent cell equation
to show the range name in its equation? If I must edit the equation, is
there some shortcut that would minimize the number of keystrokes
involved?

If I go to a range named cell and perform edit-clear-all, will the range
name also be eliminated?

Kindly do not recommend any outside provider add-ins, to do any of the
above, as they tend to freak my bosses out.

Thanks very much!
Dean

Range names
 

Thanks much - this should help
Dean

"Rowan" wrote in message
ups.com...
Hi Dean

Copying the column will not copy the range names. You could use code to
add the range names e.g:

Dim i As Integer
For i = 2 To 10
ActiveWorkbook.Names.Add "Phase." & i & "theRest", _
ActiveSheet.Cells(1, i)
Next i

This adds names to the cell in row one in columns B to I. It shouldn't
be too difficult to adapt this to your specific needs.

Hope this helps
Rowan

Dean wrote:
Thanks to both you and Rowan - this worked VERY nicely. Now, one other
thing, if I may impose:.

All these phase.1 range names refer to a phase 1 column, which will be
copied nine times with the apt cells renamed as phase.2* thru phase.10*.
I
know how to use edit replace for the formulas, but is there a way to
create
the same range names in each of the nine new columns (ten times as many
in
total) so that they start with phase.2 through phase.10, rather than (and
in
addition to) the ones that are already named with beginning with phase.1?

Thanks again very much!
Dean

"Norman Jones" wrote in message
...
Hi Dean,

If I have named a cell, can I later edit that name?

Yes

If so, will the cells that depend on it pick up the revised name?

No, the formulae will be broken. They can, however, be repaired.

For example, if formulae exist which depend on the names commencing in
'project.a' which are changed to corresponding names commencing in
'phase.1', the resultant broken formulae could be repaired with
something
like:

'==================
Public Sub RepairBrokenFormulae()
Dim rng As Range
Dim rcell As Range
Dim sStr As String

On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

For Each rcell In rng.Cells
rcell.Formula = Application.Substitute(rcell.Formula, "project.a",
"phase.1")
Next

End Sub
'<<==================

If the answer to the first question is yes, is there a way to change
say,
every range name, that currently starts with "project.a" and, say,
replace that beginning with "phase.1"?

Yes, try something like:

'==================
Public Sub EditNames()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
If nm.Name Like "project.a*" Then
ActiveWorkbook.Names(nm.Name).Name = _
Application.Substitute(nm.Name, "project.a", "phase.1")
End If
Next nm

End Sub

'<<==================


If I go to a range named cell and perform edit-clear-all, will the
range
name also be eliminated?

No, clearing the constituent cells of a named range does not delete the
range name.


---
Regards,
Norman



"Dean" wrote in message
...
Folks,

Kindly, answer whichever you can, even if you can't answer all of
these:

If I have named a cell, can I later edit that name? If so, will the
cells that depend on it pick up the revised name?

If the answer to the first question is yes, is there a way to change
say,
every range name, that currently starts with "project.a" and, say,
replace that beginning with "phase.1"?

Also, if I name an independent cell after another dependent cell's
equation already refers to it, how do I get the dependent cell
equation
to show the range name in its equation? If I must edit the equation,
is
there some shortcut that would minimize the number of keystrokes
involved?

If I go to a range named cell and perform edit-clear-all, will the
range
name also be eliminated?

Kindly do not recommend any outside provider add-ins, to do any of the
above, as they tend to freak my bosses out.

Thanks very much!
Dean