+ Reply to Thread
Results 1 to 7 of 7

Dynamic Named Range

  1. #1
    Registered User
    Join Date
    01-12-2015
    Location
    Edinburgh
    MS-Off Ver
    2010
    Posts
    3

    Dynamic Named Range

    I have a pivot table which includes a Grand Total column (currently in column AU). I want to create a named range (Grand_Total_Column) for the column that contains the Grand Total that will move with the column as the pivot table expands.
    Grand Total is in row 3.

    I have tried using this formula in the Refers To box

    =(INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH("Grand Total",$3:$3,0),4),1,"")))

    but this did not work.

    Any suggestions?
    Last edited by smack; 02-06-2015 at 07:50 AM.

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Dynamic Named Range

    Hi and welcome to the forum!

    A Grand Total in row 3? I wasn't aware that you could set it so that the Grand Total appeared at the top of the Pivot Table by default?

    In any case, it appears from the formula you are using that you are attempting to derive the cell address equivalent to a row number of 1 and a column number matching that where the Grand Total is found. But of course this single cell address will not in itself define a range: you will require the other extremity as well.

    Can you clarify? Perhaps a workbook would be of benefit here.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Dynamic Named Range

    Assuming it will always be in row 3,
    Try this

    GrndTotal =INDEX(Sheet1!$A$1:$ZZ$3,3,MATCH(9^99,Sheet1!$3:$3 ))
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    01-12-2015
    Location
    Edinburgh
    MS-Off Ver
    2010
    Posts
    3

    Re: Dynamic Named Range

    I didn't explain myself very clearly. The heading "Grand Total" of the totals column in the pivot table is always in row 3.

    I've also attached an example to see if that helps to understand what I am trying to do.example.xlsx

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Dynamic Named Range

    In your example, Grand Total is in Row 12.

  6. #6
    Registered User
    Join Date
    01-12-2015
    Location
    Edinburgh
    MS-Off Ver
    2010
    Posts
    3

    Re: Dynamic Named Range

    I want the Named Range to be for a column so the Grand Total is currently in column AU with the column heading in cell AU3

  7. #7
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Dynamic Named Range

    may be this is what you want.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Check the attached file:-
    Attached Files Attached Files
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

+ 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. [SOLVED] dynamic named range not populating combo box list if range = single cell
    By HeyInKy in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-24-2014, 05:27 PM
  2. Dynamic / Named Print Range To Include Another Range of Cells
    By thekrakenwakes in forum Excel General
    Replies: 0
    Last Post: 07-25-2014, 07:52 AM
  3. [SOLVED] creating a named range taking the avg. to date of a dynamic range.
    By siggisigg in forum Excel General
    Replies: 1
    Last Post: 07-15-2014, 08:58 AM
  4. [SOLVED] Dynamic Named Range to Auto-Populate another range based on criteria
    By BeachRock in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-24-2014, 06:46 PM
  5. Named ranges-Should I use a dynamic named range
    By foseco in forum Excel General
    Replies: 4
    Last Post: 06-11-2009, 03:56 PM

Tags for this Thread

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