Please help me convert an Excel formula into a Smartsheet formula

Beth M

The following formula works in Excel and I'd like to recreate it in Smartsheet:

=if(C2+1, D6)

In Smartsheet, my column C is called Session, and column D is called Training Date. The Session column includes numbers only.

Please let me know if you need additional info, thank you!

Beth M

https://community.smartsheet.com/discussion/comment/390073#Comment_390073

YES!!!! That gave me exactly what I was looking for!!! Thank you so much for helping me, you're amazing!!!

Carson Penticuff

Is that the entire formula? As it is written, in Excel, it would just set the value of the cell equal to that of D6 regardless of the contents of C2.

Beth M

https://community.smartsheet.com/discussion/comment/389653#Comment_389653

Thank you for your response! That is the entire formula. Column C (Session column in Smartsheet) includes a series of numbers (1, 2, 3, 4...) and they each have their own corresponding date in column D (the Training Date column in Smartsheet). So, for example: rows with a 2 in Column C have a date of 9/14/23 in column D, rows with a 3 in column C have a date of 9/21 in column D.

The cells I want to paste this formula in will display the corresponding date depending on what number is in column C (Session column in Smartsheet).

Paul Newcome

Are you able to provide screenshots for context?

Beth M

https://community.smartsheet.com/discussion/comment/389901#Comment_389901

Yes, please see attached.

Paul Newcome

Ok. I see what is going on. Try this:

=MIN(COLLECT([Training Date]:[Training Date], [Training Date]:[Training Date], @cell > [Training Date]@row))

Beth M

https://community.smartsheet.com/discussion/comment/389928#Comment_389928

Thanks! That results in #INVALID COLUMN VALUE.

Any suggestions?

Paul Newcome

Sorry. I meant to say to put that in a text/number column somewhere (just for troubleshooting).

Beth M

https://community.smartsheet.com/discussion/comment/389933#Comment_389933

Thanks! The column I'm pasting it in in Smartsheet is a text/number column. Does that help?

Paul Newcome

Shoot. Sorry. I got you mixed up with another post. That should go in a date type column since we are pulling date type data. Sorry about that.

Beth M

https://community.smartsheet.com/discussion/comment/389933#Comment_389933

Thank you! That fixed the issue of the error and it does result in a date populating the field now. However, it isn't pulling the date to the corresponding Session like it does in Excel. I need it to function just like the example in the Excel screenshot. I found an article that says that cell references in formulas are not supported in Smartsheet and I'm wondering if that's why it isn't working properly?

Please see the attached screenshot of the Smartsheet using your formula. I color coded what should match. You can see it works correctly at first but starting with the bottom half of the Session 2 group it starts to get out of whack.

Please let me know if there's any other details I can share to help.

Beth M

https://community.smartsheet.com/discussion/comment/389942#Comment_389942

Thank you so much for your help! Changing the column type to a date did fix the error I was getting. However, the dates aren't quite right. I color coded what should match in the screenshot below. As you can see ,that they start out correct, but starting with the second half of Session 2 they start to get out of whack. I'm wondering if its because Smartsheet formulas do not support cell references?

Please let me know if there are any additional details I can share to help explain. Thank you!

Paul Newcome

My apologies. I missed the Session column the firs time around.

=IFERROR(INDEX([Training Date]:[Training Date], MATCH(Session@row + 1, Session Session, 0)), "")

Beth M

https://community.smartsheet.com/discussion/comment/389993#Comment_389993

Thank you, that comes back #UNPARSEABLE :(

Paul Newcome

Sorry. Fingers weren't in tune with the brain. I used a space in place of a colon.

=IFERROR(INDEX([Training Date]:[Training Date], MATCH(Session@row + 1, Session:Session, 0)), "")

Beth M

https://community.smartsheet.com/discussion/comment/390032#Comment_390032

Awesome, getting closer! They yellow and green sections have some variability. I realized the column to the left of the Session column may help so I included that in the screenshot below. Let me know if that makes a difference.