+ Reply to Thread
Results 1 to 3 of 3

Dynamic Worksheet Reference

  1. #1
    Registered User
    Join Date
    03-16-2020
    Location
    New york
    MS-Off Ver
    2017
    Posts
    2

    Dynamic Worksheet Reference

    I need help creating a formula that will do a SUMIFS depending on the value in a cell. I have data that is broken into different worksheets dependent on month and they are all named in the same manner - 2020 January Dump - 2020 February Dump ...etc. I need a formula that will change depending on what month it says in cell k3. so for instance if k3=February, I need the formula to look in 2020 February Dump rather than 2020 January dump.

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Dynamic Worksheet Reference

    I need the formula to look in 2020 February Dump
    but what means to look , just pick up the value ?
    Give an example of your formula ..and beeter attach an Excel sample ...!
    See file attached in sheet "Info"
    Attached Files Attached Files
    Last edited by PCI; 03-16-2020 at 05:17 PM.
    - Battle without fear gives no glory - Just try

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Dynamic Worksheet Reference

    Two usual approaches: using the INDIRECT function, as in January in cell X99, then

    =SUMIFS(INDIRECT("'yada "&$X$99&" yada'!Q2:Q1001"),INDIRECT("'yada "&$X$99&" yada'!D2:D1001"),X98,INDIRECT("'yada "&$X$99&" yada'!K2:K1001"),X97)

    and an alternative when there are patterns in the worksheet names, such as month names, with those month names in X1:X12, and simpler to use defined names like

    Value_Range: =CHOOSE(MATCH($X$99,$X$1:$X$12,0),'yada January yada'!$Q$2:$Q$1001,'yada February yada'!$Q$2:$Q$1001, . . .,'yada December yada'!$Q$2:$Q$1001)
    Crit1_Range: =CHOOSE(MATCH($X$99,$X$1:$X$12,0),'yada January yada'!$D$2:$D$1001,'yada February yada'!$D$2:$D$1001, . . .,'yada December yada'!$D$2:$D$1001)
    Crit2_Range: =CHOOSE(MATCH($X$99,$X$1:$X$12,0),'yada January yada'!$K$2:$K$1001,'yada February yada'!$K$2:$K$1001, . . .,'yada December yada'!$K$2:$K$1001)

    (the . . . meaning similar arguments for March thru November), then

    =SUMIFS(Value_Range,Crit1_Range,X98,Crit2_Range,X97)

    The advantage of the 2nd approach using defined names calling CHOOSE is that it avoids volatile functions like INDIRECT. Formulas calling volatile functions recalculate whenever ANYTHING triggers recalculation. If you have a lot (1,000 or more) of formulas calling volatile functions, you could slow down recalculation substantially.

+ 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. Dynamic reference to a worksheet on a different workbook
    By DeanMoray in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-20-2019, 01:06 PM
  2. Dynamic reference for worksheet and if statement
    By Sinnie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-27-2015, 12:51 PM
  3. Copying formula - dynamic worksheet reference
    By FM1 in forum Excel General
    Replies: 2
    Last Post: 01-22-2010, 08:49 AM
  4. Dynamic Reference to other worksheet data?
    By Delarado in forum Excel General
    Replies: 6
    Last Post: 08-17-2009, 12:42 PM
  5. Dynamic worksheet reference in formulas
    By Beedz in forum Excel General
    Replies: 2
    Last Post: 09-29-2008, 04:27 PM
  6. Dynamic worksheet reference
    By erasor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2006, 09:35 AM
  7. dynamic worksheet reference
    By bmccall17 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-19-2005, 02:05 PM

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