+ Reply to Thread
Results 1 to 7 of 7

Dynamic Named Range w/o Using VBA

  1. #1
    Registered User
    Join Date
    02-20-2014
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Dynamic Named Range w/o Using VBA

    Hello,

    I have a dataset that is downloaded. It comes in a series of tables with a cell that identifies which table. I can search for "Late Finish Date" and that tells me which row it's table starts at. And, I can search for the next table which tells me the last row of "Early Start Date" and the first row of "Early Finish Date." If you look at my spreadsheet, you'll see the row number where I found "Early Start Date" in CELL E4 (Value=922). The row where I found "Early Finish Date" is in CELL E5.

    I want to use those values to redefine my named range when I refresh the dataset. In the Name Manager tool, I added this formula: = OFFSET(A1,(E4),1) : OFFSET(A1,(E5-2),4). Excel changes it to: = OFFSET('NewComparison (002)'!A1,('NewComparison (002)'!E4),1) : OFFSET('NewComparison (002)'!A1,('NewComparison (002)'!E5-2),4). This appears to work. It selects the right cells when I am using the tool. But, I can't get it to work with a formula, even with a simple INDEX function. The range name doesn't show up in that box on the top left of the spreadsheet.


    Is there another way?

    Thanks
    Attached Files Attached Files
    Last edited by shawjohn42; 10-31-2018 at 09:05 AM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: Dynamic Named Range w/o Using VBA

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Last edited by AliGW; 10-31-2018 at 09:16 AM.

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,702

    Re: Dynamic Named Range w/o Using VBA

    Quote Originally Posted by shawjohn42 View Post
    ..... It selects the right cells when I am using the tool. But, I can't get it to work with a formula, even with a simple INDEX function. The range name doesn't show up in that box on the top left of the spreadsheet.


    Is there another way?

    Thanks
    Dynamic named ranges (and other named formulas) do not show up in that box.

    When typing your INDEX formula and you are at the point where the dynamic named range goes hit the F3 function key. You will have a list that includes the dnrs. Click on the one you want and Excel will enter it into the formula.

    Does this help?
    Dave

  4. #4
    Registered User
    Join Date
    02-20-2014
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Dynamic Named Range w/o Using VBA

    Dave,

    Thank you for your response. I tried the INDEX function, clicked F3, and was able to select the ndr. But, the range changed from A23:F6350 to J1046767:M1046769. So, I think there must be a better way to define the range.

  5. #5
    Registered User
    Join Date
    02-20-2014
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Dynamic Named Range w/o Using VBA

    Thanks for the note. I uploaded my file. And, I added notes trying to explain my need as well as columns intended to be populated using VLOOKUP.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,936

    Re: Dynamic Named Range w/o Using VBA

    One option, create a dynamic named range for the raw data table:
    Raw_Data
    Please Login or Register  to view this content.
    Then use it to create the AFD, etc.'s

    Bens_AFD:
    Please Login or Register  to view this content.
    Ben Van Johnson

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,702

    Re: Dynamic Named Range w/o Using VBA

    Another detail regarding the DNR definition in post #1:

    Try setting the first address argument of the OFFSET function to absolute

    = OFFSET($A$1,(E4),1) : OFFSET($A$1,(E5-2),4)

    Otherwise that address will change relative to where you are entering your INDEX formula and where active cell was when you defined the DNR in Name Manager.

+ 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. Replies: 0
    Last Post: 03-08-2016, 02:25 PM
  2. [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
  3. 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
  4. [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
  5. [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
  6. [SOLVED] Dynamic Named Range Help - Range Based on Values in Column
    By Filibuster in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-03-2012, 11:13 AM
  7. 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