Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Naming cells (2003)

    Hi again - need to know if there is a work-around naming cells to use in formulas with more than one sheet selected at once.

    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Naming cells (2003)

    I do not understand the question. Care to elaborate?

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Naming cells (2003)

    Example: Making 3 time sheets for 3 different employees simultaneously. Name Cell A2 "pay rate" to use in a formula to compute each person's daily pay based on hours worked. When I ungroup the sheets, sheets 2 and 3 no longer have the same "pay rate" reference in the formula that they had B4 I ungrouped the sheets. I understand the problem - the named cell refers to the first sheet - i.e., Joe Blow - clear now? Probably not - if it isn't, no big - I'll just work around it the hard way.

    Have a good one - again, thanks.


    Aunt Linda

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Naming cells (2003)

    If you use Insert/Name/Define and enter the name as shown in the first image below, then the name will be defined on Sheet1 only. After you click the Add button, the name will appear in the list as in the second image below with the sheet name on the right side. This way, you can define the same name on each sheet.
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Naming cells (2003)

    Oh, yeah. Now I realize I have done that before (in reference to my other reply in this thread), but it's kind of a tedious process to localize the name over and over.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Naming cells (2003)

    I don't know of a way to do what you want through the menus, I may have something to learn. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> However, if you create the range name on one sheet and copy it, you'll get the same name localized to each sheet

    Here's a basic macro that will name the single active cell on each grouped worksheet to "test", localized to each sheet, so your problem should be avoided. Change "test" to what you want. As written the macro won't provide valid results with multi-cell ranges (it will use just the top left cell).

    Sub RepName()
    Dim wks As Worksheet
    Dim strRC As String
    strRC = "R" & ActiveCell.Row & "C" & ActiveCell.Column
    For Each wks In ActiveWindow.SelectedSheets
    wks.Names.Add Name:="test", RefersToR1C1:="=" & wks.Name & "!" & strRC
    Next wks
    Set wks = Nothing
    End Sub

    (If this capability is in Jan Karel's Name Manager, I couldn't figure out where or how to use it. Jan Karel, maybe "replicate one name locally" would be a useful feature?)
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Naming cells (2003)

    Yes it is tedious if you have lots of range names.

    Alternately you could 1 sheet with containing the named ranges and instead of having a separate payrate named for each, you could create a named payrate table and lookup the proper rate in the table using the person's name or ID.

    It makes the formulas easy to read, and you have 1 place to edit all the payrates.
    Steve

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Naming cells (2003)

    Using the new "add name" dialog available from the main screen of my Name Manager greatly simplifies adding local names.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Naming cells (2003)

    Hi John,
    FWIW, if you replace your <pre>strRC = "R" & ActiveCell.Row & "C" & ActiveCell.Column</pre>

    with <pre>strRC = selection.Address(referencestyle:=xlR1C1)</pre>

    it should work with multi-cell selections.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Naming cells (2003)

    You are SOOOOOOOOOO smart - my students and I thank you very, very much

    Happy Thanksgiving


    Aunt Linda

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Naming cells (2003)

    Thanks, Rory. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Naming cells (2003)

    Read the reply from Legare - haven't tried it yet but I bet it works! Thanks for the reply

    Happy Thanksgiving

    Aunt Linda

Posting Permissions

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