Add Dynamic Reference in Formula
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
-
=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
-
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!
-
Are you able to provide some screenshots for reference?
-
Hi,
I Hope these screenshots help,
Thanks,
-
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")
-
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! 😊
-
=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)))
-
Thanks so much @Mike TV!
-
Yes. It looks like you just had a misplaced closing parenthesis and didn't close out the first COUNTIFS.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!