IF/AND statements in Smartsheet with unparseable error

Sherry L

Can using ranges from another sheet work when attempting to use IF/AND statements in Smartsheet? For example:

=IF(AND({Disclosure Test Range 1} = TODAY(), {Disclosure Name} = "Name of Item Needed", {Disclosure Test Range 2}, {Disclosure Test Range 1}))

I am currently getting a #unparseable error and I think it is based on requesting to return multiple conditions for whole column ranges and not a specific cell. Basically what the formula states is if the date in a column equals today's date and is equal to a specific disclosure name, if true then return the associated comment, otherwise if false return the date. Anyone have a suggestion or am I thinking about this wrong? I also attempted the INDEX and COLLECT functions in other formulas trying to accomplish the same and received #unparseable as well.

Paul Newcome

What exactly are you wanting the formula to accomplish?

Sherry L

Basically what the formula states is if the date in a column equals today's date and is equal to a specific disclosure name, then return the associated comments, otherwise if false return the date that is in the column.

Paul Newcome

Are you able to provide some screenshots for reference?

Sherry L

I have posted a screen shot with redacted info.

Paul Newcome

Try something like this...

Insert a new date type column onto the sheet where you are wanting to pull the comments to and enter this formula:

=MAX(COLLECT({Source Sheet Dates Column}, {Source Sheet Name Column}, @cell = [Name Column]@row))

Then the formula to pull the comments will be:

=INDEX(COLLECT({Source Sheet Comments Column}, {Source Sheet Name Column}, [Name Column]@row, {Source Sheet Dates Column}, [Max Date Column]@row), 1)

Sherry L

Thank you. Unfortunately, neither works and I still receive #unparseable for both formulas.

Paul Newcome

Neither should be throwing that error. Are you able to provide screenshots of the formulas in the sheet similar to the screenshot below as well as copy/paste the formulas directly from the sheet to here?

Sherry L

I will not know what row on the reference sheet has the info I am looking for since these are recipient responses from Smartsheet Forms that are sent out awaiting recipients to respond to specific questions and I don't know what row item number each will be assigned. The recipients do not all respond at the same time and each are responding based off different disclosure names. In the index formula I see you have [max date column]@row, 1 which I do not know what that would align with as we would be looking to find info in the whole column and not just a row.

=MAX(COLLECT({Disclosure Test Range 6}, {Disclosure Name}, @cell = [Network Management Boilerplate]@row))

Paul Newcome

Right. On the sheet where we are putting this formula to pull the data, we need to first collect the MAX date. We do this with a MAX/COLLECT in its own column because you cannot nest a COLLECT function inside of a COLLECT function.

What is in the [Network Management Boilerplate] column?

Sherry L

Network Management Boilerplate is one of the names in the Name Column and one of the criteria that I need the info pulled back on along with either if there is a comment or not aligned with the name.

Paul Newcome

Ok. Then Range 6 should be the date column in the source sheet. It should be providing a date which the second formula (the INDEX/COLELCT one) uses the result of that as part of its criteria.

Sherry L

Paul I think I see where the issue is but not sure. I changed my sheet name to a new name but all the columns in that sheet when being referenced in a formula reflects correctly as being pulling from the new sheet name except one column. And that one column is the one that I am using in the formula. How do I get the remaining column to reflect the new sheet name and not the old name that no longer exist when being referenced in a formula? I feel this is the reason why my IF statement is not working since the old sheet name no longer exist. Just a suspicion as its the only thing I see that could be the issue.

Paul Newcome

You can enter the cell as if you are going to edit the formula, click inside of the reference, then click on the link to "Edit Reference".

Which IF statement are you referring to?

Sherry L

Yes, I know. I am saying that even though that I have been editing the formulas continually comes back with the old sheet name for that one column. No matter how I build the formula if I reference that column in the same sheet it is not updated to the new sheet name. It's the only column that did not update to reflect the correct new sheet name.

Paul Newcome

It still shouldn't be causing a problem. Did you try editing the sheet name portion of the reference?

Which IF formula are you referring to?