Automatically define names

G

Guest

Guest
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Yep looked and was perfect.

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

Sam
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top