Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Automatically define names

  1. #1
    Guest

    Default

    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.

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Guest

    Default

    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


  4. #4
    Guest

    Default

    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.

  5. #5
    Guest

    Default

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

    Sam

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Guest

    Default

    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.


  9. #9
    Guest

    Default

    Yep looked and was perfect.

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

    Sam

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You're welcome Sam.

    Cheers, Nate

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •