+ Reply to Thread
Results 1 to 6 of 6

Absolute References using Named Ranges?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-12-2014
    Location
    Boston, MA
    MS-Off Ver
    Office 2010
    Posts
    172

    Absolute References using Named Ranges?

    Hello Gurus!

    I'm trying to change some of my formulas that currently use regular ranges to Named Ranges. I need to do a fair amount of copying over, and I thought that Named Ranges were automatically set to absolute references, but when I copy over, my ranges are acting as Relative References. Does anyone know if this is a setting or if there is a way to write in the "$"'s to make them Absolute References?

    I hope that's clear! Thanks!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,489

    Re: Absolute References using Named Ranges?

    If you select a range and give it a name in the Name Box (where the cell reference appears on the Formula Bar), you will get absolute references.

    If you are using Name Manager to define a range, you will need to type in the dollar ($) signs.

    Other than that, I'm not sure what you mean.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    11-12-2014
    Location
    Boston, MA
    MS-Off Ver
    Office 2010
    Posts
    172

    Re: Absolute References using Named Ranges?

    I used the Name Manager to give my tables names. Is using the Name Box different? How so?

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,489

    Re: Absolute References using Named Ranges?

    If you select the range before you open Name Manager and then select new and type a name, it will have pre-populated the range with the selected cells. In that respect, it's no different.

    If you add a new range with only one cell selected, the new range will refer to that cell. If you want to extend it, you need to put your own dollar signs in.

  5. #5
    Forum Contributor
    Join Date
    11-12-2014
    Location
    Boston, MA
    MS-Off Ver
    Office 2010
    Posts
    172

    Re: Absolute References using Named Ranges?

    I think there might be a slight disconnect in what I'm asking, but I think I did learn an issue and I'm fixing it.

    I was asking why when I copied a cell with a formula referencing named ranges over one cell, the named ranges I was referencing were also moving over. However, I think I was actually using a mix of Named Ranges and Table Header names. I did not understand there was such a difference until now. The way I went about this was I originally had tables, but in my lookup formulas, I was not using Table Header names; I was just using the actual reference (AH:AH as opposed to "Group"). The reason I decided to use named ranges and Table Headers now is because the number of lookups referencing entire columns I was using throughout my workbook was causing it to run painfully slow. So I went to Name Manager, and changed the Table Names from their generic "Table 1", "Table 2", etc names to names that made sense to me. Then, in the formulas, I changed them to say =COUNTIFS(NewTableName[HeaderName],A2,NewTableName[2ndHeaderName],"Count"). When I copied the formulas over to adjacent cells, they would change to =COUNTIFS(NewTableName[2ndHeaderName],NewTableName[3rdHeaderName]), as in they were acting as relative references. I'm now actually defining the columns I am referencing in formulas, and they are acting as absolute references as I thought they should.

    So there you have it; a long detailed explanation for something you probably already fully understood. Hopefully someone else gets good use out of this though!

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,489

    Re: Absolute References using Named Ranges?

    Ah, now I see the disconnect. If you copy a Table Header from one column, it will auto-adjust as though it is a relative reference.

    There is a workaround ... you need to duplicate the header name! Makes for some awfully long formulae. You would refer to something like:
    Formula: copy to clipboard
    table1[[column1]:[column1]]


    See this link for more details: http://www.excelcampus.com/tips/abso...uctured-table/

    Thanks for the rep by the way

    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Relative References Named Ranges
    By cmore in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-07-2015, 03:14 PM
  2. Print named ranges and references
    By NMullis in forum Excel General
    Replies: 2
    Last Post: 11-16-2007, 02:08 PM
  3. [SOLVED] Absolute Named Ranges????
    By SMBR in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-31-2006, 02:15 PM
  4. Named ranges: don't want absolute references
    By sonicblue in forum Excel General
    Replies: 0
    Last Post: 11-22-2005, 10:05 AM

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