+ Reply to Thread
Results 1 to 3 of 3

Dynamic indirect reference with named lists

  1. #1
    Registered User
    Join Date
    06-20-2013
    Location
    LondOn, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Dynamic indirect reference with named lists

    Hi experts,

    I am trying to sum over multiple sheets dependent on multiple criteria.
    I reproduce below the formula in one of the cells which is summing certain cells on sheets dependent on whether the sheet is on a named list.
    So in the below example and attachment, any sheet defined as “Merchant” and matching the account description in cell a12, the value contained in cell a12 of any sheets defined as ‘Merchant’ will be returned. Therefore, the value 76,490 is returned from sheets ‘MPA4T’ and ‘MPAPP’ as both are on the named list.

    =SUMPRODUCT(SUMIF(INDIRECT("'"&Merchant&"'!"&CELL("address",$A12)),$A12,INDIRECT(Merchant&"!"&CELL("address",$P12))))


    This is all working fine. However I am struggling to make the ‘Merchant’ bit dynamic. Thus I want to insert a cell reference so that when I copy across I am not having to hard code to ‘King’ etc… Please help.

    Sbz
    Attached Files Attached Files
    Last edited by sabiza; 02-24-2020 at 06:10 AM. Reason: File not attached

  2. #2
    Registered User
    Join Date
    01-24-2020
    Location
    Belgium
    MS-Off Ver
    2013
    Posts
    95

    Re: Dynamic indirect reference with named lists

    Still, INDIRECT func come into service

    Add a new name "INTERCHANGE" with the name manager
    name.png

    Then replace Merchant in ur original formula with INDIRECT(INTERCHANGE)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Voilà, u can fill down or across without hardcoding any more.

    Cheers!
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-20-2013
    Location
    LondOn, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Dynamic indirect reference with named lists

    ThxAlot,

    Indeed Voila. That works beautifully.

    Thanks a lot!

+ 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] INDIRECT cannot be used in a dynamic named range OFFSET
    By BNCOXUK in forum Excel General
    Replies: 18
    Last Post: 09-19-2019, 09:48 AM
  2. Indirect Data Validation of Dynamic Named Ranges
    By frostedflakes in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-07-2018, 12:20 PM
  3. Replies: 1
    Last Post: 10-07-2015, 07:45 AM
  4. Dynamic Named Ranged & Indirect Data Validation
    By Voluntondile in forum Excel General
    Replies: 4
    Last Post: 10-01-2014, 08:12 PM
  5. Offset and Indirect with Dynamic Named Ranges
    By nickmangan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-07-2014, 10:42 PM
  6. Dynamic Named Ranges & Drop Down Lists
    By student6 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-02-2014, 01:07 AM
  7. [SOLVED] Adding new species to dynamic named lists
    By CharlieGough in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-19-2013, 10:56 AM

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