#INVALID OPERATION - Error
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!
Answers
-
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)
-
Paul I entered this formula in exactly as shown above and receive an "#INVALID VALUE" error this time.
-
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.
-
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?
-
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?
-
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.
-
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.
-
Are you able to provide a screenshot of your source data?
-
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
-
I don't see a column name of "[Rock Bin #1]" in either of the sheets.
-
That's what it is referring to.
-
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)
-
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.
-
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.
-
Interesting....There were definitely blanks as I just had one entry in there for testing purposes. All is well now!
Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!