+ Reply to Thread
Results 1 to 7 of 7

Referring to a named range

  1. #1
    Registered User
    Join Date
    01-29-2013
    Location
    texas
    MS-Off Ver
    Excel 2010
    Posts
    68

    Referring to a named range

    Hi all,

    Named range = TheYear on a sheet named Master.
    On a different sheet:

    If I enter in the formula bar in cell (1, 7) =TheYear, I get in cell(1, 7) "=TheYear"

    If enter the same info in some random cell I get 2012, I've tried changing the format of cells but it
    has no effect. I can't seem to find a pattern to the cells it works in.

    thanks,

  2. #2
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Referring to a named range

    When you check the name manager, is the scope set to workbook or sheet?
    it needs to be workbook to work on every sheet.
    Regards
    Sean

    Please add to my reputation if you think i helped
    (click on the star below the post)
    Mark threads as "Solved" if you have your answer
    (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:
    [code] Your code here [code]
    Please supply a workbook containing example Data:
    It makes its easier to answer your problem & saves time!

  3. #3
    Registered User
    Join Date
    01-29-2013
    Location
    texas
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: Referring to a named range

    Yes scope is set to workbook, it works in some cells and not in others on the same sheet.

  4. #4
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Referring to a named range

    hmm, cant duplicate it on mine.
    can you upload an example?

  5. #5
    Registered User
    Join Date
    01-29-2013
    Location
    texas
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: Referring to a named range

    The sheets will need to be unprotected.

    thanks
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Referring to a named range

    The cell you were having problems with was formatted as text.
    if you delete the value,change it to general , then enter =theyear it will work.
    at least it did on mine

  7. #7
    Registered User
    Join Date
    01-29-2013
    Location
    texas
    MS-Off Ver
    Excel 2010
    Posts
    68

    Re: Referring to a named range

    Thanks Sean,

    It seems the formats must match and the cell formatted before entering the range.

    I appreciate the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1