+ Reply to Thread
Results 1 to 3 of 3

Dynamic named range - INDEX - acting strange!

  1. #1
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Dynamic named range - INDEX - acting strange!

    Hey everyone,

    I had a dynamic named range which started at top of myworksheet (row 2) and I decided I would like to move this range down to row 36 and use the top of my worksheet for something else. Then the named range got weird...

    My original dynamic named range was as follows:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I modified it to the following so that whatever I include in the top rows doesn't get counted in the dynamic range:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    For some reason, when I test out the new named range above, it suddently turns into the following formula (or some variation of this):
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Any idea why this is happening?? I don't get it... I just want a dynamic named range to begin a little further down in worksheet.

    Thanks for any help you provide,

    Geoff.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Dynamic named range - INDEX - acting strange!

    You need to use absolute references on both the column AND rows..

    =Template1!$F$36:INDEX(Template1!$F$36:$F$60000,COUNTA(Template1!$E$36:$E$60000),1)

  3. #3
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Dynamic named range - INDEX - acting strange!

    Ahhhh right! Thank you

    I thought the problem was more conceptual... so that's where I was looking for a solution.

    Cheers Jonmo1!

    Geoff.

+ 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. Dynamic Named Range + Index/Match Returning Circular Reference
    By jennarenae in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-29-2014, 06:23 PM
  2. [SOLVED] Strange adress from named range in VBA
    By Hammer_757 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-16-2014, 10:55 AM
  3. Subroutine is halted, but Excel acting strange - Cells don't appear selected...
    By paulcola in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 05-05-2013, 08:00 PM
  4. 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
  5. Excel acting strange
    By mrgeek2u in forum Excel General
    Replies: 5
    Last Post: 06-07-2007, 04:54 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