IF Statement result brings back another cell value
Good morning everyone!
I am fairly new to Smartsheet so please spare my dumb question but I really need the help of the experts solving a formula logic:
Scenario:
I have a list of Events, Attendees and a column with a IF = Month(Today()) formula based on the event date to know what Events took place in the current Month.
Desired result:
On a Dashboard, use 2 Metric widgets to display which Event in the Current Month had the most Attendees and which Event in the Current Month had the less Attendees.
Can you please help me build the logic and functions for both cases?
I tried something like
IF(AND({Current Month? Range} = "Yes", {Number of Attendees Range} = MAX(()),
And this is where I am having problems telling the formula to give me the value of the Event Name column.
Please excuse me if I am not using the correct statements.
JS
Best Answers
-
Hi @Jorge Silva,
Not a "dumb" question at all! There may be a simpler way to get the same result, but I think this might work for you for the highest attended event:
=INDEX([Event Name]:[Event Name], MATCH(MAX(COLLECT([Number Of Event Attendees]:[Number Of Event Attendees], [Current Month]:[Current Month], ="Yes")), [Number Of Event Attendees]:[Number Of Event Attendees], 0))
And for the lowest attended event, try:
=INDEX([Event Name]:[Event Name], MATCH(MIN(COLLECT([Number Of Event Attendees]:[Number Of Event Attendees], [Current Month]:[Current Month], ="Yes")), [Number Of Event Attendees]:[Number Of Event Attendees], 0))
-
Please try the following formula for ( Number of Event Attendees )
Event Score =IF(AND([Current Month]@row = "Yes", MAX([Number of Event Attendees]:[Number of Event Attendees]) = [Number of Event Attendees]@row), "Highest", IF(AND([Current Month]@row = "Yes", MIN([Number of Event Attendees]:[Number of Event Attendees]) = [Number of Event Attendees]@row), "Lowest"))
and convert it to column format formula, the following screenshot shows the result:
you can test the soluation in this puplished Sheet link:
and the formula that check the current month is ( change it to column format formula )
Current Month =IF(MONTH(TODAY()) = MONTH([Event Start Date]@row), "Yes", "No")
and use the following conditional formating to highlight the row
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
Hi @Jorge Silva
Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hello @Bassam Khalil
Many thanks for replying. See below an example of my sheet:
I would like a formula to give me the Event Name based on: first, is in the Current Month and then, the number of Event Attendees is the highest (in the current Month). The same on another formula but the other way around, the Event Name with the lowest Number of Attendees (in the current month).
The idea behind that is to automate a dashboard so with each passing month it gives me the Events with more and less attendees, without me having to go each month and change the source cell manually.
I hope this helps.
Many many thanks in advance.
JS
-
Hi @Jorge Silva,
Not a "dumb" question at all! There may be a simpler way to get the same result, but I think this might work for you for the highest attended event:
=INDEX([Event Name]:[Event Name], MATCH(MAX(COLLECT([Number Of Event Attendees]:[Number Of Event Attendees], [Current Month]:[Current Month], ="Yes")), [Number Of Event Attendees]:[Number Of Event Attendees], 0))
And for the lowest attended event, try:
=INDEX([Event Name]:[Event Name], MATCH(MIN(COLLECT([Number Of Event Attendees]:[Number Of Event Attendees], [Current Month]:[Current Month], ="Yes")), [Number Of Event Attendees]:[Number Of Event Attendees], 0))
-
Please try the following formula for ( Number of Event Attendees )
Event Score =IF(AND([Current Month]@row = "Yes", MAX([Number of Event Attendees]:[Number of Event Attendees]) = [Number of Event Attendees]@row), "Highest", IF(AND([Current Month]@row = "Yes", MIN([Number of Event Attendees]:[Number of Event Attendees]) = [Number of Event Attendees]@row), "Lowest"))
and convert it to column format formula, the following screenshot shows the result:
you can test the soluation in this puplished Sheet link:
and the formula that check the current month is ( change it to column format formula )
Current Month =IF(MONTH(TODAY()) = MONTH([Event Start Date]@row), "Yes", "No")
and use the following conditional formating to highlight the row
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Many thanks @John Pudar and @Bassam Khalil for your answers! Both worked and I will apply them for different approaches in the context of the final report and dashboard.
😊
Thank You!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!