IF/AND cross-sheet formula not working

Michaela Kamenska

Hi all,

I'm trying to create a column formula but only select one date out of 2 available. Current layout of my sheet is as below:

What I'm trying to teach to the system is that if Global lead is not equal to X and Regional Start-Up Date (green) is " ", then use "Lead Start- Up Date", else use "Regional Start-Up date....".

I've got this but it doesn't seem to come out right, getting an #INVALID OPERATION error.

See the formula below:

=IF(AND({Global Lead} <> "EU", {Regional start-up} = ""), {Lead Start-Up Date}, {Regional Start-up})

mattstoc

This has been fun since I am learning smartsheet. I created a similar spreadsheet. The following formula works for me:

=IF(AND([Global Lead]@row <> "EU", [Regional Startup Date]@row = ""), [Lead Startup Date]@row, [Regional Startup Date]@row)

Notes:

The result column is formatted as a date data type

It looks like your formula is trying to apply to the whole column, I did my to a row.

Michaela Kamenska

Hi @mattstoc ,

thank you but what you outlined is a formula where all rows/columns are in the same sheet as the formula. What I am trying to do is a cross-sheet formula so I am pulling a single column information into another sheet, however, I need certain criteria to be met. But let me try something different based on that.

Michaela Kamenska

what I also forgot to mention is that I need to match [project code]@row to {project code} in the external sheet. Omg, this is getting tangled and my brain is not working anymore....

Paul Newcome

Are you able to explain exactly what you are trying to do? Why are you wanting to pull a date from another sheet and how do you intend to use it?

Kaleb Riley

Michaela,

I am almost wondering if a nested if statement would be a better option for this...

Maybe something like

=IF({Global Lead} <> "EU", IF(ISDATE({Regional start-up}) = false, {Lead Start-Up Date}, {Regional Start-up}))

Also I prefer using either ISDATE or ISBLANK when trying to determine if a cell is empty or not. Maybe try messing around with these concepts and see if it works?

Michaela Kamenska

Hi @Paul Newcome,

I have two types of dates (Lead startup and Regional Startup). Depending who is the global lead for the project, there MAY be a different startup for lead region and secondary region. Sometimes they start at the same time, in other situations they don't. If they start at the same time, only Lead Start date is entered.

I am trying to to pull these dates from source sheet into another sheet that I was tasked with for resourcing and FTEs so I can create breakdowns of hours. I wanted to try and avoid adding more columns to my second sheet and match the specific entry to the project code. Now I sorted it by adding additional columns into my sheet (these will be hidden) and the formula now reads:

=IFERROR(IF([Global Lead]@row = "EU", INDEX({Lead Start}, MATCH([Study #]@row, {study #}, 0)), IF(AND([Global Lead]@row <> "EU", [Regional Start-Up]@row = ""), INDEX(Lead Start}, MATCH([Study #]@row, {study #}, 0)), INDEX({Regional Start}, MATCH([Study #]@row, {study #}, 0)))), "")

I am just not entirely happy about having had to add "Regional Start-Up" and "Global Lead" columns to my sheet which I am working on.

Paul Newcome

Based on your initial description I had a feeling we were going to end up with some sort of INDEX/MATCH but wanted to be sure. I am glad you were able to get it figured out on your own.

The only thing I would suggest to do differently is use slightly different logic for the IF and nest that inside of the INDEX/MATCH instead of nesting multiple INDEX/MATCHes inside of an IF. It will be a little easier to manage if adjustments need made and takes less for the sheet's back-end to process (and references a lot fewer cells since each range is only referenced once).

=IFERROR(INDEX(IF(OR([Regional Start-Up]@row = "", [Global Lead]@row = "EU"), {Lead Start}, {Regional Start}), MATCH([Study #]@row, {study #}, 0)), "")