+ Reply to Thread
Results 1 to 8 of 8

SumIFS with dynamic sheet reference not using INDIRECT

  1. #1
    Registered User
    Join Date
    07-11-2019
    Location
    Thailand
    MS-Off Ver
    2010
    Posts
    4

    SumIFS with dynamic sheet reference not using INDIRECT

    i use in my excel table sumifs and countif in conjunction with INDIRECT to summarize data from various sheets. The data is in various sheets because the 30km project is managed in different section for which different people are responsible. so a single data worksheet is not practical
    i know that INDIRECT can be replaced by CHOOSE or INDEX however i cannot figure out how it could work in this case.
    is there any solution to it or do i need to use VBA?
    Attached Files Attached Files

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

    Re: SumIFS with dynamic sheet reference not using INDIRECT

    Welcome to the forum.

    It's important for us to know why you don't want to use INDIRECT. Is it because the workbooks will not be open? Or something else?

    Do you have the PowerQuery add-in?
    Ali


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

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: SumIFS with dynamic sheet reference not using INDIRECT

    For the formulas used and the layout of your sheet, INDIRECT is the only practical solution. To use CHOOSE or INDEX in place of INDIRECT means entering each sheet and range into the formula, you would not be able to refer to column A for the sheet name.
    If your only reason for it is to tidy the sheet up and clear the errors caused by sheets not yet created, then IFERROR would be the best choice.

  4. #4
    Registered User
    Join Date
    07-11-2019
    Location
    Thailand
    MS-Off Ver
    2010
    Posts
    4

    Re: SumIFS with dynamic sheet reference not using INDIRECT

    INDIRECT is volatile and thus recalculates every time i make a change.
    way too slow

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: SumIFS with dynamic sheet reference not using INDIRECT

    Have you considered manual calculation, or short circuiting the volatile functions (which is effectively manual calculation of selected formulas)?

    edit:- example to short circuit, based on the current formula in C12.

    First go to File > Options > Formulas and check the box for 'enable iterative calculation'

    Enter the formula into C12,

    =IF(LEN($N$1),SUMIFS(INDIRECT($A12&"!aa:aa"),INDIRECT($A12&"!D:D"),$B$4,INDIRECT($A12&"!G:G"),C$6)+SUMIFS(INDIRECT($A12&"!ae:ae"),INDIRECT($A12&"!D:D"),$B$4,INDIRECT($A12&"!G:G"),C$6),C12)

    When N1 is empty, the existing value in C12 is shown without recalculation. When N1 is not empty, the full formula is recalculated as volatile, effectively switching calculation on or off for any short circuited formula by simply typing into, or deleting the content of N1.
    Last edited by jason.b75; 07-15-2019 at 04:20 AM.

  6. #6
    Registered User
    Join Date
    07-11-2019
    Location
    Thailand
    MS-Off Ver
    2010
    Posts
    4

    Re: SumIFS with dynamic sheet reference not using INDIRECT

    Thank you for valuable comments and suggestions.
    So I guess I have only 2 choices then, either go for manual recall or rebuild the workbook into a single data sheet
    Any other ideas?

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: SumIFS with dynamic sheet reference not using INDIRECT

    IF the use of indirect is purely to make entry of the formulas easier then maybe create a direct formula using less direct methods.

    Try entering this formula into No Anchorages C12, then copy it to C12:I29, K12:Q29,S12:Y29 and AA12:AG29.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Next copy, and paste special > values on the same range.

    Finally, use Ctrl h to do a find and replace of = with = (replace all).

    The above steps allow you to create a whole formula as a text string, then convert it to a valid formula (with volatile functions removed).

  8. #8
    Registered User
    Join Date
    07-11-2019
    Location
    Thailand
    MS-Off Ver
    2010
    Posts
    4

    Re: SumIFS with dynamic sheet reference not using INDIRECT

    Hi Jason
    thank you for this clever trick, works like a charm

+ 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. How to reference a dynamic tab in excel (maybe indirect, but not sure)
    By GABY1234 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-13-2019, 05:17 AM
  2. Insert dynamic column reference into SUMIFS formula?
    By piroshkilla in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-08-2019, 08:08 PM
  3. [SOLVED] INDEX MATCH with dynamic reference using INDIRECT
    By Avaritia in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-11-2019, 11:09 PM
  4. Using INDIRECT formula to reference a dynamic range on another sheet
    By Travisty in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-01-2018, 12:22 PM
  5. Replies: 1
    Last Post: 12-18-2017, 06:46 AM
  6. VBA Dynamic Range - An Indirect Cell Reference
    By ExcelHelp2013 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2013, 11:08 AM
  7. [SOLVED] Dynamic VLOOKUP wit INDIRECT reference
    By supern0va in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-29-2012, 11:44 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