Please help me convert an Excel formula into a Smartsheet formula
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!
Best Answer
-
YES!!!! That gave me exactly what I was looking for!!! Thank you so much for helping me, you're amazing!!!
Answers
-
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.
-
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).
-
Are you able to provide screenshots for context?
-
Yes, please see attached.
-
Ok. I see what is going on. Try this:
=MIN(COLLECT([Training Date]:[Training Date], [Training Date]:[Training Date], @cell > [Training Date]@row))
-
Thanks! That results in #INVALID COLUMN VALUE.
Any suggestions?
-
Sorry. I meant to say to put that in a text/number column somewhere (just for troubleshooting).
-
Thanks! The column I'm pasting it in in Smartsheet is a text/number column. Does that help?
-
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.
-
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.
-
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!
-
My apologies. I missed the Session column the firs time around.
=IFERROR(INDEX([Training Date]:[Training Date], MATCH(Session@row + 1, Session Session, 0)), "")
-
Thank you, that comes back #UNPARSEABLE :(
-
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)), "")
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
