+ Reply to Thread
Results 1 to 12 of 12

Indirect Function won't accept the Named Range

  1. #1
    Registered User
    Join Date
    05-14-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    21

    Indirect Function won't accept the Named Range

    Hi,
    I have a formula using an Indirect function which normally works. However in this instance it won't accept the Named Range. The cell is looking in a number of workbooks and summing cells that meet two conditions. I have attached the workbook, any support here is greatly appreciated. The Cell with the formula is =IF(OR($A22="",C$21=""),"",SUMPRODUCT(SUMIF(INDIRECT("'"&_Payrun&"'!$E$7:$AE$7"),C$21,INDIRECT("'"&_Payrun&"'!R[0]C5:R[0]C31",FALSE)))). The Cell is returning #NAME.

    Michael

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Indirect Function won't accept the Named Range

    The workbook wasn't attached - see the yelow banner at the top of the forum.
    Indirect needs workbooks to be open in order to evaluate correctly. Are these other workbooks open?

    What's the name of the named range?
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,886

    Re: Indirect Function won't accept the Named Range

    ....INDIRECT("'"&_Payrun&"'!R[0]C5:R[0]C31",FALSE)))) should perhaps be
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    ??

    ( I haven't checked the number of parenthesis )

  4. #4
    Registered User
    Join Date
    05-14-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    21

    Re: Indirect Function won't accept the Named Range

    Hi,
    Sorry, the Workbook was too big. I have trimmed it down so it should be attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-14-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    21

    Re: Indirect Function won't accept the Named Range

    In response to your question about Named Range, it is called Payrun.

    Michael

  6. #6
    Registered User
    Join Date
    05-14-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    21

    Re: Indirect Function won't accept the Named Range

    Hi Pepe,
    This didn't seem to work. I have attached the workbook to the post.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2301 (Windows 11 22H2 64-bit)
    Posts
    65,646

    Re: Indirect Function won't accept the Named Range

    The named range is Payrun. In the formula you have _Payrun. Remove the underscore.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  8. #8
    Registered User
    Join Date
    05-14-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    21

    Re: Indirect Function won't accept the Named Range

    Hi Ali,
    I have it reading the Named Range now, but it is coming up with #REF. So I'm getting closer!! See attached:
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2301 (Windows 11 22H2 64-bit)
    Posts
    65,646

    Re: Indirect Function won't accept the Named Range

    Did you use the Evaluate Formula feature on the Formulas ribbon to find whatís triggering the error? Iím away from the laptop now until tomorrow morning.

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 Version 2301, rarely Office 2007
    Posts
    12,887

    Re: Indirect Function won't accept the Named Range

    In Name Manager I tried changing the definition of _Payrun from =OFFSET('Pay Roll Report'!$B$55,0,0,COUNTIF('Pay Roll Report'!$B$55:$B$68 ,"*?"),1) to =OFFSET('Pay Roll Report'!$B$55,0,0,COUNTIF('Pay Roll Report'!$B$55:$B$57,"*?"),1) The original was searching for more sheets (14) than your workbook has (3).

    The formula now returns
    C
    D
    E
    F
    G
    14
    1.2.04
    1.2.02
    1.2.03
    1.1.02
    1.1.03
    15
    0
    0
    31.5
    0
    0
    Dave

  11. #11
    Registered User
    Join Date
    05-14-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    21

    Re: Indirect Function won't accept the Named Range

    Hi FlameRetired,
    Thanks, that's awesome!

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 Version 2301, rarely Office 2007
    Posts
    12,887

    Re: Indirect Function won't accept the Named Range

    You are welcome. Glad it helped. Thank you for the feedback and added rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as 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: 1
    Last Post: 10-16-2019, 07:46 AM
  2. Max(indirect(named range)) causes #REF
    By netgroover in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-08-2016, 06:36 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