+ Reply to Thread
Results 1 to 4 of 4

Using INDIRECT with a dynamic range name as the array in an INDEX formulae = #REF

  1. #1
    Registered User
    Join Date
    06-05-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    9

    Using INDIRECT with a dynamic range name as the array in an INDEX formulae = #REF

    Hi
    This is my first post - I hope someone can help.
    I am trying to use INDEX to search through an array which is itself a dynamic range name, the name for which is entered in a separate cell.
    A simple example is attached.
    "dynamic" is the name of the range which will vary according to the inputs for "rows" in cell F1 and "columns" in cell F2.
    The range name "dynamic" is then entered in a separate cell F3.
    The formula I thought would work is shown in cell, which is INDEX(INDIRECT(F3),1,1), which I thought would match R1C1 in the "dynamic" range, but as you can see return a #REF error.
    Can anyone see where this is going wrong. The reason for doing it this way is I want to be able to change the value of F3 for different range names and the formula to look in that range name, without having to change the formula itself.
    Thanks in anticipation...
    Attached Files Attached Files

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Using INDIRECT with a dynamic range name as the array in an INDEX formulae = #REF

    INDIRECT doesn't work with dynamic ranges. you have to use the EVALUATE XLM function but you can only do that inside a named formula so you need to define another name using the formula =EVALUATE(F3) and then use =INDEX(new_name,1,1)
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    06-05-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Using INDIRECT with a dynamic range name as the array in an INDEX formulae = #REF

    Thanks, I think that works, I've got a few variations to work through but all good so far. Much appreciated...

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Using INDIRECT with a dynamic range name as the array in an INDEX formulae = #REF

    you're welcome :-)

    please don't forget to mark the thread solved (click the 'thread tools' link at the top, then 'mark solved')

+ 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: 15
    Last Post: 09-10-2013, 05:31 AM
  2. Dynamic array in Index
    By nagaguru in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-01-2013, 09:01 PM
  3. Indirect and Dynamic Range
    By Graham Haughs in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-03-2006, 03:40 AM
  4. Range Name from Another Workbook conflicts with INDEX and INDIRECT
    By SubDoer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-14-2006, 05:50 AM
  5. Replies: 2
    Last Post: 05-18-2005, 11:42 PM

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