+ Reply to Thread
Results 1 to 7 of 7

#NA Error in index function when using the indirect function to get a named range.

  1. #1
    Registered User
    Join Date
    10-25-2021
    Location
    Chicago, IL USA
    MS-Off Ver
    2016
    Posts
    3

    #NA Error in index function when using the indirect function to get a named range.

    I am having a problem with an Index formula that uses an INDIRECT command to look for a named.
    The formula works if I use the named range directly, but when using the INDIRECT command in the formula if returns #N/A.
    Here is the formula
    =INDEX(CY_SALES_ACCOUNT,MATCH(1,INDEX((INDIRECT("CY_SALES_"&'Control Sheet'!F8)=LARGE(CY_SALES_SEPTEMBER,ROWS(A$6:A6)))*(COUNTIF(A$5:A5,CY_SALES_ACCOUNT)=0),0)))

    The named range I am testing with is CY_SALES_SEPTEMBER I am using this formula =OFFSET(Current!$K$4,0,0,COUNTA(Current!$K:$K)-2,1)

    I could use some pointer of what to do with this problem.
    Thanks
    Last edited by 1629dill; 10-25-2021 at 05:39 PM.

  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
    44,091

    Re: #NA Error in index function when using the indirect function to get a named range.

    It would help to know what the working formula looks like, the name of the Named Range, and then content of the Named Range.

    Ideally, please see the yellow banner at the top of the page.
    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
    Registered User
    Join Date
    10-25-2021
    Location
    Chicago, IL USA
    MS-Off Ver
    2016
    Posts
    3

    Re: #NA Error in index function when using the indirect function to get a named range.

    I tried to post a sample excel file but I was not allows to bu the site.

    The Named range looks like this:
    =OFFSET(Current!$K$4,0,0,COUNTA(Current!$K:$K)-2,1)

    Thanks

  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
    44,091

    Re: #NA Error in index function when using the indirect function to get a named range.

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: #NA Error in index function when using the indirect function to get a named range.

    Have you tried to confirm the array formula by Ctrl-Shift-Enter Combination? Try it.
    If it does not work, try to post a sample WS.
    Quang PT

  6. #6
    Registered User
    Join Date
    10-25-2021
    Location
    Chicago, IL USA
    MS-Off Ver
    2016
    Posts
    3

    Re: #NA Error in index function when using the indirect function to get a named range.

    Here is a sample of the problem.
    The problem is on Sheet3 cell B6
    Thanks
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: #NA Error in index function when using the indirect function to get a named range.

    Can't help with why the INDIRECT(named range... formula isn't working, however perhaps this could be used instead.
    On the Current sheet convert the range A3:N16 into a table.
    Use the following formula in cells B3 and down on Sheet3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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] Sheet Named Range lookup/Indirect function syntax
    By QSGuy in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-13-2021, 10:20 PM
  2. Indirect Function won't accept the Named Range
    By Plastik mac in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-07-2020, 11:23 PM
  3. Indirect function with Named Range for use as a PT Data Source
    By andikeep2580 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-20-2015, 12:07 PM
  4. Replies: 9
    Last Post: 04-01-2015, 04:23 PM
  5. Replies: 2
    Last Post: 06-03-2013, 08:26 AM
  6. Named range lookup using Index/Indirect/Match Function calls in Macro
    By sgopan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-27-2013, 07:51 PM
  7. Using Indirect function to return a named range.
    By adrianodl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-21-2012, 09:30 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