Automatically define names

Hello All.

I was wondering if it is possible to automatically define names based on a formula.

For example if cell a1 = yes, name a1 "YES1". if cell a5 = yes, name a5 "YES2". Etc...

I hope the above is clear enough to understand. Thankyou all in advance for your help.

[Damon Ostrander]

Hi Anonymous,

No, I don't believe a worksheet formula or function can do this because formulas are not allowed to change anything other than the contents of the cell containing the formula, but especially not properties of other cells.

What you describe could very easily be accomplished via a macro, however, and by triggering it from a worksheet Change event it would be made to work with exactly the functionality you describe. Let me know if you wish to explore this further.

Hi Damon,

Thanks for your response. Sorry it took me a little while to reply, had a very busy couple of days.

I would be very interested in achieving this through the use of a macro.

Unfortunately my macro skills are not at the level they should be!!

Here is an example of what I would like to do.

A
1
2 Yes
3
4
5 No
6
7 Yes

Cell A2 I want to define as Answer1, Cell A5 as Answer2 and Cell A7 as Answer3. The sheet could have upwards of 200 names to define over 6000 rows so going through each becomes a little cumbersome.

Any help would be greatly appreciated.

Thankyou again Damon,
Sam

Hi again Sam,

Here's a little macro that I believe does what you want:

Option Base 1

Private Sub YesNames()
Static Index As Long
Dim Addr As Variant
Dim Cell As Range
Dim i As Integer
Addr = Array("a2", "a5", "a7")
For i = 1 To UBound(Addr)
Set Cell = Range(Addr(i))
If UCase(Cell.Value) = "YES" Then
Index = Index + 1
Cell.Name = "Yes" & Index
End If
Next i
End Sub

As you can see, it only does the three cells you mentioned, and skips cells containing "No". You can increase the size of the list to about 30 entries with the Array statment, but beyond that you would have to set the array elements, e.g.,

Dim Addr(100) As String

Addr(1) = "a3"
Addr(2) = "a2"
Addr(3) = "a7"
.
.
.
Addr(99) = "a304"
Addr(100) = "a333"

I hope I interpreted you problem correctly.

Thanks mate. I'll plug it in and give it a go and let you know how it works.

Sam

[NateO]

I'll throw the following into the mix, names cells in column B (if you want column A, see next post (assuming your answers are in column B (change the range selection if not))):

Sub Nameit()
i = 1
Application.ScreenUpdating = False
Range("b1:b6000").Select
For Each Cell In Selection
If ActiveCell <> "" Then
ActiveWorkbook.Names.Add Name:="Answer" & i, RefersToR1C1:=ActiveCell
i = i + 1
End If
ActiveCell.Offset(1, 0).Select
Next Cell
Range("a1").Select
Application.ScreenUpdating = True
End Sub


Cheers,
Nate

[ This Message was edited by: NateO on 2002-02-21 17:36 ]

[NateO]

I guess if had values in column b but wanted to name the cells in column a (which look like row numbers), you could use the following:

Sub Nameit2()
i = 1
Application.ScreenUpdating = False
Range("b1:b6000").Select
For Each Cell In Selection
If ActiveCell <> "" Then
ActiveWorkbook.Names.Add Name:="Answer" & i, RefersToR1C1:=ActiveCell.Offset(0, -1)
i = i + 1
End If
ActiveCell.Offset(1, 0).Select
Next Cell
Range("a1").Select
Application.ScreenUpdating = True
End Sub

Cheers,

Nate

Thanks NateO,

Looks perfect. I'll let you know.

Do you guys realise how much time you save everyone who has questions here! Excellent work.

Yep looked and was perfect.

You have saved me an enormous amount of time. Thankyou very much NateO.

Sam

[NateO]

You're welcome Sam.

Cheers, Nate