#INVALID OPERATION - Error

Kevin Rice · 07/14/20

I'm trying to write an IF/AND formula that references a different sheet to return a single row value. I think where I'm running into a problem is that I need the formula to evaluate three entire columns of data to see if the criteria are met, then return a row level value if all criteria are met on that single row. New data will be entered into this sheet regularly at scheduled intervals so the format of the referenced sheet needs to stay as is.

This is the formula I'm trying to write to evaluate the columns in the picture below.

=IF(AND({Rock Bin Date Column} = TODAY() - 1, {Rock Bin Time} = Time@row, {Rock Bin} = [Rock Bin #1]1), {Level}, 0)

The end result I'm trying to get from this formula would be 82, given the picture above.

Any help is greatly appreciated!

Paul Newcome · 07/14/20

Try looking into an INDEX/COLLECT. Something along the lines of...

=INDEX(COLLECT({Level}, {Rock Bin Date Column}, @cell = TODAY() - 1, {Rock Bin Time}, @cell = Time@row, {Rock Bin}, @cell = [Rock Bin #1]1), 1)

Kevin Rice · 07/14/20

https://community.smartsheet.com/discussion/comment/256523#Comment_256523

Paul I entered this formula in exactly as shown above and receive an "#INVALID VALUE" error this time.

Paul Newcome · 07/14/20

Make sure that error is not present anywhere within the source data. Also double check that all cells in {Rock Bin Date Column} contain a date and that the source column for that range is set as a date type column in the column properties.

Kevin Rice · 07/14/20

I don't see any errors in the source sheet and that column is set up as the date type.

I do however see that in my source sheet the {Rock Bin Time} column is formatted as a Single-Select Dropdown menu while the "Time" Column in the Rollup sheet is a Text/Number field. Could this cause the issue?

Paul Newcome · 07/14/20

The dropdown vs text type shouldn't make a difference in this instance because they both contain text strings.

Let's take a look at this section...

{Rock Bin Time}, @cell = Time@row, {Rock Bin}, @cell = [Rock Bin #1]1

You have "Time@row" and "[Rock Bin #1]1"

Is there a reason you specified row 1?

Kevin Rice · 07/14/20

Not necessarily, no sir. A person could very easily alter that to read [Rock Bin #1] = 1, 2, 3, etc.

In row 1 is where the actual number 1 resides in the [Rock Bin #1] Column.

Kevin Rice · 07/14/20

Trying it that way actually didn't work either. If I take that out and just put a 1 in there it gives me an @UNPARSEABLE error.

Paul Newcome · 07/14/20

Are you able to provide a screenshot of your source data?

Kevin Rice · 07/14/20

I have a form embedded in a Dashboard that an operator enters these values into then submits them to this sheet.

[Date] Column is: Date Type

[Time] Column is: Dropdown Single-Select

[Rock Bin] is: Dropdown Single-Select

[Level] is: Text/Number

Paul Newcome · 07/14/20

I don't see a column name of "[Rock Bin #1]" in either of the sheets.

Kevin Rice · 07/14/20

That's what it is referring to.

Paul Newcome · 07/15/20

Lets try this...

=INDEX(COLLECT({Level}, {Rock Bin Date Column}, IFERROR(@cell, TODAY()) = TODAY() - 1, {Rock Bin Time}, @cell = Time@row, {Rock Bin}, @cell = [Rock Bin #1]1), 1)

Kevin Rice · 07/15/20

That one right there did the trick! It returned the 82 I was looking for.

Thank you very much! Now I've got some other sheets doing roughly the same thing I'm going to try to apply this formula to.

Paul Newcome · 07/15/20

It looks like the issue was with the {Rock Bin Date Column} range. It either has blanks/non-date values, or that same error in it somewhere.

Kevin Rice · 07/15/20

Interesting....There were definitely blanks as I just had one entry in there for testing purposes. All is well now!

Thanks!

#INVALID OPERATION - Error

Answers

Help Article Resources

Categories