Need some help with syntax for if formula that also contains left statement and references other sma

SHOOD

Good afternoon!

I am trying to grab some data from one smartsheet and populate into another smartsheets based on a few filters.

Smartsheet 1

Workstream column must be Curriculum & Advising

The left most 4 characters in the Objective column must match the Objective column in Smartsheet 2

If those two conditions are met, take the Sub Area column from Smartsheet 2 and populate the Sub Workstream column in Smartsheet 1, otherwise do not populate the Sub Workstream column in Smartsheet 2.

Here is what I wrote which is not working at all :)

=IF(Workstream@row="Curriculum & Advising") and left(Objective@row, 4)={WashU_Workset Playbook Range 3}),{WashU_Workset Playbook Range 4},blank)

Thanks for your help, I really appreciate it!!!

Susan

Katy H

Susan, this may require a helper column since you are technically searching for a value in the WashU_Workset Playbook and then pulling the corresponding value from a separate column. There might be a more elegant way to pull this off but I would recommend a "helper" column in each sheet, perhaps just simply called "Workstream + Objective".

The formula in this column would combine those two values together and the formula would look like this: =Workstream@row + " - " + LEFT(Objective@row, 4)

Then to perform the desired task mentioned above you would instead use an INDEX/MATCH formula to pull this information in.

=INDEX({WashU_Workset Playbook Range 4}, MATCH([Workstream + Objective]@row, {Workstream + Objective}, 0))

I hope that makes sense!

Katy H

Susan, this may require a helper column since you are technically searching for a value in the WashU_Workset Playbook and then pulling the corresponding value from a separate column. There might be a more elegant way to pull this off but I would recommend a "helper" column in each sheet, perhaps just simply called "Workstream + Objective".

The formula in this column would combine those two values together and the formula would look like this: =Workstream@row + " - " + LEFT(Objective@row, 4)

Then to perform the desired task mentioned above you would instead use an INDEX/MATCH formula to pull this information in.

=INDEX({WashU_Workset Playbook Range 4}, MATCH([Workstream + Objective]@row, {Workstream + Objective}, 0))

I hope that makes sense!

SHOOD

Thank you Katy! I am not currently an Admin on one of the Smartsheets so I will have to request the column be added before I can try your solution but I really appreciate your suggestion!

SHOOD

I still don't have my formula quite right.

When I use the following formula I get "not right" which I think I should have found a match:

=IFERROR(INDEX(COLLECT({WashU_Workset Playbook Sub-Area}, {WashU_Workset Playbook Ref ID#}, [Susan Objective-Test DNU]@row, {WashU_Workset Playbook Workstream#}, "Curriculum & Advising"), 1), "not right")

I broke down the formula to see if I can just do the match part and I receive #NO MATCH:

=MATCH([Susan Objective-Test DNU]@row, {WashU_Workset Playbook Ref ID#}, 0)

Once again I believe it should have found a match so do I need to convert one or both of the columns in the =MATCH formula to Numbers or Characters?

Thank you for your help!

Susan

Katy H

@SHOOD A few follow up questions:

• Did you add the helper columns I mentioned? These are critical in building the formula I initially suggested.
• Try removing the "IFERROR" and put in the formula as is so we can see what the error code is you receive then, knowing the exact error code is essential to diagnosing the issue.
• Are you able to show the cross sheet references you built to see what type of data is being referenced in each column?