Multiple IF/OR Formula Question

Jstein13

Hi All!

I can't seem to get this formula figured out for what I need it to do. I believe it's a combination of =IF and =OR but haven't been able to figure it out.

What I'd like is for the Aggregate Column to read any of the names of the games if the date in the column that precedes it is 9/2/23. For example, in line one, I'd like to to return back "101 - Michigan vs. FL State and 102 - LSU vs. Clemson"

Kelly Moore

Hey @Jstein13

There's some formulas that will need to be built to help you do what you need to do. You will need to extract the date and the game portions from the dropdown list response. This means the format of how the information appears in the dropdown must be consistently entered. This is important. The parsing (aka extraction) can happen in hidden columns on the intake sheet above, or on the destination sheet.

Here's your Date formula (using the format where there is a single space before the date portion begins.

=IFERROR(DATE(VALUE(RIGHT([Game - Date]@row, 4)), VALUE(MID([Game - Date]@row, FIND("/", [Game - Date]@row) - 1, 1)), VALUE(MID([Game - Date]@row, FIND("/", [Game - Date]@row) + 1, LEN([Game - Date]@row) - 5 - FIND("/", [Game - Date]@row)))), "")

Here's the Game portion. You can have whatever you like on this side of the Date portion, with the exception of a slash mark. I've used the slash of the Date as the anchor of where the text ends and the date begins. This means you can have whatever you like in the text portion - as long as there is a space then the date begins and the date format includes a slash or some other special character that would not appear in the text side.

=LEFT([Game - Date]@row, FIND("/", [Game - Date]@row) - 2)

Let me or the community know if we can help more

Kelly

Kelly Moore

Hey @Jstein13

The formula below will provide what you requested:

=(IF([Game 1 Date]@row <= DATE(2023, 9, 2), [Game 1 Request]@row) + CHAR(10) + IF([Game 2 Date]@row <= DATE(2023, 9, 2), [Game 2 Request]@row) + CHAR(10) + IF([Game 3 Date]@row <= DATE(2023, 9, 2), [Game 3 Request]@row) + CHAR(10) + IF([Game 4 Date]@row <= DATE(2023, 9, 2), [Game 4 Request]@row))

If you plan on adding additional columns, you may want to reconsider your data setup. The opportunity in your current set up is smartsheet doesn't realize the column [Game 1 Request] is associated with [Game 1 Date], thus you will always have to manually indicate the relationship as your Date/Request pairs grow across the row.

If you went vertically down a sheet, then then smartsheet can associate the Request to the Date, making the data MUCH easier to collect. You may be able to obtain the data using a report, depending on how the data needed to be formatted, and not even need a formula. If wanting to use the Summary Sheet fields on the same sheet, or collect in a column on this sheet, the formula is below.

=JOIN(COLLECT([Game Request]:[Game Request], [Game Date]:[Game Date], <=DATE(2023, 9, 2)), ", ")

Jstein13

Thanks @Kelly Moore! I appreciate all the options. Ultimately, what I'm trying to do is this.

We have a number of clients that need to be able to make requests of which game(s) during the season they want to go to which ideally I'd like to collect in a form. Each person gets 4 potential choices which then once all the requests are made we need to take and combine everyone's choice for a game on 9/2 with their name and preference order (ex. first choice) and then everyone's choices for 9/9, etc. so we can determine who gets what games.

Currently we obtain the information in a form and then export to excel to manipulate the data. In a perfect world I'd keep everything in Smartsheet.

If you have any thoughts how that might be best accomplished I'm all ears.

Thanks again!

Kelly Moore

Hey @Jstein13

I do have one more option that could work.

Create a sheet like this for data entry for your clients (you will publish the sheet so that it can be accessible to anyone without sharing the sheet directly). I forgot to add the name column - we would build it so they would put name or email - whichever ONE you need in one cell and populate cells 2-4 via formula. When using a published document, once the URL is shared then it is that URL that is accessed, not the actual object (in this case not the sheet itself).

The dropdown I used looked like this (I just made up teams and dates, as an example)

I then use this automation combination to copy the rows to the final sheet where we can then easily calculate or manipulate, and also clear the sheet for the next person

Would this work for you?

Kelly

Jstein13

I think this will work! Thanks so much for all of your help, Kelly!

Kelly Moore

Hey @Jstein13

There's some formulas that will need to be built to help you do what you need to do. You will need to extract the date and the game portions from the dropdown list response. This means the format of how the information appears in the dropdown must be consistently entered. This is important. The parsing (aka extraction) can happen in hidden columns on the intake sheet above, or on the destination sheet.

Here's your Date formula (using the format where there is a single space before the date portion begins.

=IFERROR(DATE(VALUE(RIGHT([Game - Date]@row, 4)), VALUE(MID([Game - Date]@row, FIND("/", [Game - Date]@row) - 1, 1)), VALUE(MID([Game - Date]@row, FIND("/", [Game - Date]@row) + 1, LEN([Game - Date]@row) - 5 - FIND("/", [Game - Date]@row)))), "")

Here's the Game portion. You can have whatever you like on this side of the Date portion, with the exception of a slash mark. I've used the slash of the Date as the anchor of where the text ends and the date begins. This means you can have whatever you like in the text portion - as long as there is a space then the date begins and the date format includes a slash or some other special character that would not appear in the text side.

=LEFT([Game - Date]@row, FIND("/", [Game - Date]@row) - 2)

Let me or the community know if we can help more

Kelly