Reference multiple columns on another sheet to return value

Jim08

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.

bisaacs

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!