#INVALID OPERATION - Error

Kevin Rice

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

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

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

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

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

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

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

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

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

Kevin Rice

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

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

Kevin Rice

That's what it is referring to.

Paul Newcome

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

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

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

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

Thanks!