IF Statement result brings back another cell value

Jorge Silva

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

John Pudar

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))

Bassam Khalil

@Jorge Silva

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 Khalil

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.

Jorge Silva

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

John Pudar

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))

Bassam Khalil

@Jorge Silva

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

Jorge Silva

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!