Add Dynamic Reference in Formula

Options
A Rose
A Rose ✭✭✭✭✭
edited 09/29/22 in Formulas and Functions

Hi,

I'm creating a summary sheet,

I have a few sheets with the same columns, and I want to add COUNTIF formulas to the summary sheet,

I'd like to know if there's any way that the Reference should be a dynamic option, so if a user chooses on the Reference sheet from a dropdown column: Sheet 1, the formulas knows to use reference #1 in a hidden column or sheet summary column?

I tried creating a column with an IF formula, that IF dropdown column is sheet 1, it should say the reference of sheet 1, but it seems like the COUNTIF looks at the IF column instead of actually using the reference to look it up.

it will always return 0, because there are 0 of that match in the IF column...

Thank you!

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @A Rose

    =IF([Sheet Number]# = "1", COUNTIFS({Sheet 1 Range 1}, Status#, {Sheet 1 Range 2}, >=[From Date Entered]#, {Sheet 1 Range 2}, <=[Until Date Entered]#, {Sheet 1 Range 3}, >=[From Date Status Updated]#, {Sheet 1 Range 3}, <=[Until Date Status Updated]#, {Sheet 1 Range 4}, HAS(@cell, Reason@row)), IF([Sheet Number]# = "2", COUNTIFS({Sheet 2 Range 1}, Status#, {Sheet 2 Range 2}, >=[From Date Entered]#, {Sheet 2 Range 2}, <=[Until Date Entered]#, {Sheet 2 Range 3}, >=[From Date Status Updated]#, {Sheet 2 Range 3}, <=[Until Date Status Updated]#, {Sheet 2 Range 4}, HAS(@cell, Reason@row)))

Answers

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    @Paul Newcome @Andrée Starå

    You guys probably bumped into this in the past, both of you helped me a lot in the past...!

    Please let me know if you got any solution/workaround for this.

    Thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide some screenshots for reference?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You would have to build the ranges into the nested IF which in turn is built into the COUNTIFS.


    =COUNTIFS(IF(Option# = "A", {Range For A}, IF([Option# = "B", {Range For B})), "criteria")

  • A Rose
    A Rose ✭✭✭✭✭
    edited 10/03/22
    Options

    Hi @Paul Newcome ,

    I got the idea on how to use the formula based on the sheet #, however my formula is much more complexed then what I shared originally, so I'll share with you the real formula (and change the reference names...)

    Here's what worked - if it's only sheet 1:

    =IF([Sheet Number]# = "1", COUNTIFS({Sheet 1 Range 1}, Status#, {Sheet 1 Range 2}, >=[From Date Entered]#, {Sheet 1 Range 2}, <=[Until Date Entered]#, {Sheet 1 Range 3}, >=[From Date Status Updated]#, {Sheet 1 Range 3}, <=[Until Date Status Updated]#, {Sheet 1 Range 4}, HAS(@cell, Reason@row)))

    Here's what didn't work (Incorrect Argument Set) when trying to add if Sheet is Sheet 2,

    =IF([Sheet Number]# = "1", COUNTIFS({Sheet 1 Range 1}, Status#, {Sheet 1 Range 2}, >=[From Date Entered]#, {Sheet 1 Range 2}, <=[Until Date Entered]#, {Sheet 1 Range 3}, >=[From Date Status Updated]#, {Sheet 1 Range 3}, <=[Until Date Status Updated]#, {Sheet 1 Range 4}, HAS(@cell, Reason@row), IF([Sheet Number]# = "2", COUNTIFS({Sheet 2 Range 1}, Status#, {Sheet 2 Range 2}, >=[From Date Entered]#, {Sheet 2 Range 2}, <=[Until Date Entered]#, {Sheet 2 Range 3}, >=[From Date Status Updated]#, {Sheet 2 Range 3}, <=[Until Date Status Updated]#, {Sheet 2 Range 4}, HAS(@cell, Reason@row))))

    Thank you for your help! 😊

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @A Rose

    =IF([Sheet Number]# = "1", COUNTIFS({Sheet 1 Range 1}, Status#, {Sheet 1 Range 2}, >=[From Date Entered]#, {Sheet 1 Range 2}, <=[Until Date Entered]#, {Sheet 1 Range 3}, >=[From Date Status Updated]#, {Sheet 1 Range 3}, <=[Until Date Status Updated]#, {Sheet 1 Range 4}, HAS(@cell, Reason@row)), IF([Sheet Number]# = "2", COUNTIFS({Sheet 2 Range 1}, Status#, {Sheet 2 Range 2}, >=[From Date Entered]#, {Sheet 2 Range 2}, <=[Until Date Entered]#, {Sheet 2 Range 3}, >=[From Date Status Updated]#, {Sheet 2 Range 3}, <=[Until Date Status Updated]#, {Sheet 2 Range 4}, HAS(@cell, Reason@row)))

  • A Rose
    A Rose ✭✭✭✭✭
    Options
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Yes. It looks like you just had a misplaced closing parenthesis and didn't close out the first COUNTIFS.

  • A Rose
    A Rose ✭✭✭✭✭
    edited 10/06/22
    Options

    Hi @Paul Newcome and @Mike TV,

    (*updated)

    I want to upgrade this formula a bit... I don't know how to,

    I want to add that if one of the Summary date columns are empty it shouldn't count that IF. (Something like if it's blank skip this COUNTIFS...)

    I tried to do it if the cell in reference is empty that worked: OR(@cell >= [From Date Entered]#, @cell = ""),

    But how do I do if sheet summary is a date or empty...?

    Thank you! 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!