+ Reply to Thread
Results 1 to 9 of 9

INDIRECT -- need cell range to change to the INDIRECT method

  1. 12-04-2018, 09:05 AM #1
    skydivetom
    skydivetom is offline
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    342

    INDIRECT -- need cell range to change to the INDIRECT method

    Experts:

    I need some assistance with changing a formula and replacing the "static" cell range by using the INDIRECT method.

    I have attached an XLS which (hopefully) provides sufficient additional details explaining exactly what I would like to change. The XLS contains the following:
    Tab "Data" -- contains the source data
    Tab "Age" -- includes the "Current Method" where the cell range (Data!A2:A11) is included in the formula. This tab also includes the "Desired Method" where I would like the INDIRECT approach and use another cell range (lookup table) so that I can easily modify my cell range and have a more dynamic approach.

    Please see the call out (in the XLS) which provides additional information. As of right now, my formula throws an error... any help as to how I need to update the formula would be greatly appreciated.

    Thank you in advance!
    EEH
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-04-2018, 09:42 AM #2
    Richard Buttrey
    Richard Buttrey is offline
    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 -- need cell range to change to the INDIRECT method

    Hi,

    Try in G3 copied to G7

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    G8 is a slightly different formula but hopefully you can follow the logic of the indirect above and change it. If not just post back.
    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.
    Reply With QuoteRegister To Reply
  3. 12-04-2018, 09:43 AM #3
    rorya
    rorya is offline
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,915

    Re: INDIRECT -- need cell range to change to the INDIRECT method

    The INDIRECT part is actually fine. The issue is that you've constructed a SUMIF type criteria pair, which is not what SUMPRODUCT needs. It should be:

    =SUMPRODUCT(--(INDIRECT("'Data'!"&O$2&":"&P$2)<=R2),--(Data!$A$2:$A$11>=$Q$2),--(Data!$F$2:$F$11<=F3),--(Data!$F$2:$F$11>=E3))
    Rory
    Reply With QuoteRegister To Reply
  4. 12-04-2018, 09:43 AM #4
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: INDIRECT -- need cell range to change to the INDIRECT method

    Does this work?

    =SUMPRODUCT((INDIRECT("'Data'!"&O$2&":"&P$2)<=""&R2)*(Data!$A$2:$A$11>=$Q$2)*(Data!$F$2:$F$11<=F3),--(Data!$F$2:$F$11>=E3))

    It produces data with no errors but I havent checked if its the correct result
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
    Reply With QuoteRegister To Reply
  5. 12-04-2018, 10:11 AM #5
    skydivetom
    skydivetom is offline
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    342

    Re: INDIRECT -- need cell range to change to the INDIRECT method

    rorya & Special-K:

    Thank you for the prompt response... I truly appreciate it.

    I apologize if I wasn't fully clear in the Excel... the formula contains four (4) elements where I currently reference **row 2:11**.
    =SUMPRODUCT(--(Data!$A$2:$A$11<=$R$2),--(Data!$A$2:$A$11>=$Q$2),--(Data!$F$2:$F$11<=B3),--(Data!$F$2:$F$11>=A3))

    I need all four elements to be in sync. So, if O2 and P2 changes from e.g., A2 & A11 to, e.g., A3 & A7, respectively, I then would need all four elements (A columns <=R2, A columns >= Q2; F columns <= B3, F columns >= A3) to be updated with the INDIRECT method.

    How would the formula look like afterwards?

    EEH
    Reply With QuoteRegister To Reply
  6. 12-04-2018, 10:14 AM #6
    rorya
    rorya is offline
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,915

    Re: INDIRECT -- need cell range to change to the INDIRECT method

    Where are you putting F2 and F11, or aren't you? If you aren't, just enter the row numbers in O2 and P2 and use:

    =SUMPRODUCT(--(INDEX('Data'!$A:$A,O$2):INDEX('Data'!$A:$A,P$2)<=R2),--(INDEX('Data'!$A:$A,O$2):INDEX('Data'!$A:$A,P$2)>=$Q$2),--(INDEX('Data'!$F:$F,O$2):INDEX('Data'!$F:$F,P$2)<=F3),--(INDEX('Data'!$F:$F,O$2):INDEX('Data'!$F:$F,P$2)>=E3))
    Reply With QuoteRegister To Reply
  7. 12-04-2018, 10:18 AM #7
    skydivetom
    skydivetom is offline
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    342

    Re: INDIRECT -- need cell range to change to the INDIRECT method

    ... sorry... and Richard too
    Reply With QuoteRegister To Reply
  8. 12-04-2018, 10:21 AM #8
    Richard Buttrey
    Richard Buttrey is offline
    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 -- need cell range to change to the INDIRECT method

    Here's a Pivot Table approach.

    Use the slicer if necessary to select the date range you're interested. However if the only reason for the O2 & P2 cells was to make sure all tne data was covered, with the PT there's no need since it is the object of a dynamic range name called 'tbl' which expands automatically as new data is added
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 12-04-2018, 11:18 AM #9
    skydivetom
    skydivetom is offline
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    342

    Re: INDIRECT -- need cell range to change to the INDIRECT method

    It works now...

    I would like to thank everyone for contributing to this solution. Thousand thanks!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  2. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Andy Wiggins in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 06:05 AM
  3. [SOLVED] Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Andy Wiggins in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 04:05 AM
  4. [SOLVED] Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  5. [SOLVED] Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  6. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  8. [SOLVED] Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-05-2005, 11:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1