Reference multiple columns on another sheet to return value
Hi, I'm looking for a formula to reference across two columns on a different sheet. On the destination sheet I have Project Name and Date I want to pull back. On the source sheet I want to pull from Project Name, Expected Start Date, and Stop Date. I played with automation but couldn't get it to do exactly what I wanted because it'll only copy a new row, not modify existing. Everything on the destination sheet will start out empty.
The formulas I'm looking for would give me
- IF expected start date or end date have a date NOT in the Past, return Project Name and Date to their respective cells.
-IF expected start date and end date have the same date NOT in the paste, return Project Name and Date to their respective cells.
-IF expected start date and end date both have a date but are different dates and are NOT in the past, create two rows, one with each date but the same project name, and return Project Name and Dates to their respective cells.
I have been taking the smartsheet university courses but this feels like it's a little more difficult than the examples I've learned from there so far.
Thank you for your help.
Answers
-
Hey @Jim08,
Let's go step by step on how we could build out the formula. For the first formula, you started your requirement for this with an "if", so that'll be the first thing we put:
=IF()
Then you stated if the start date OR end date is NOT in the past, then return something. This can be a little tricky to figure out the order of operations on what should go first, so we'll take a look at the Formula Handbook, and specifically the Glossary that's in there. I went to the "OR()" section and tested where the "NOT()" operator could be placed, and it looks like we'll want to put it outside the "OR()" operator:
=IF(NOT(OR()))
Now, let's add the logic in:
=IF(NOT(OR([Start Date] < TODAY(), [End Date] < TODAY())))
Looks good so far! Now we have to add in the results of this logical expression:
=IF(NOT(OR([Start Date] < TODAY(), [End Date] < TODAY())), [Project Name], "")
You won't necessarily be able to use one formula for both cells, you'll need each cell for the Project Name and the Date to have their own formulas. To reference other sheets, you'll get a small popup window appear when you're typing out the formula with a link to reference other sheets:
So in the formula above where the
[Start Date]
[End Date]
and[Project Name]
are, you'd click that link and navigate to the sheet and cell you're trying to reference.Hope this is helpful!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!