#INVALID OPERATION with INDEX(COLLECT

Michelle St George

Completely Stumped. For all intents & purposes, I have the same formula in two columns and one is working and one is not. Would be grateful for a solution.

The Stage:

I am trying to retrieve 'yesterday's' Meter Reading (Gallons) into today's row to calculation consumption between yesterday and today. (The Formula does interrogate an indicator to see if the data is missing for the previous day so it can indicate that if needed)

• All 'Meter Reading' columns are Text/Number
• Meter Reading (Cubic Feet) formula:
  • =IF([Measurement Type]@row = "Cubic Feet", [Meter Reading]@row) *Where [Meter Reading]@row is the result of a form entry
• Meter Reading (Gallons) formula:
  • =IF([Measurement Type]@row = "Cubic Feet", ([Meter Reading]@row / 7.48052), [Meter Reading]@row)

The working formula:

=IF([Missing Previous Day]@row = 0,

INDEX(COLLECT([Meter Reading (Cubic Feet)]:[Meter Reading (Cubic Feet)],

[Site ID]:[Site ID], [Site ID]@row,

[Form Submission Date]:[Form Submission Date], [Form Submission Date]@row - 1), 1), "Data NA")

The non-working formula (last column in picture above showing #INVALID OPERATION):

=IF([Missing Previous Day]@row = 0,

INDEX(COLLECT([Meter Reading (Gallons)]:[Meter Reading (Gallons)],

[Site ID]:[Site ID], [Site ID]@row,

[Form Submission Date]:[Form Submission Date], [Form Submission Date]@row - 1), 1), "Data NA")

NOTE: 100 points to anyone that can enlighten me

Paul Newcome

There must be something wrong with the [Meter Reading (Gallons)] column then.

Make sure all filters are off. Then create a new filter on that column and start it out where that column "is one of". Then it should give you a dropdown to select from. See if there are any error messages listed in that.

Paul Newcome

Is that particular error present in any of the columns being referenced by the formula? If it is in even one cell, it will pull that error through.

Michelle St George

It is not. I even converted the non-working formula to a cell formula (instead of column) just to see if the error would still appear

Paul Newcome

What does this output:

=COUNTIFS([Site ID]:[Site ID], [Site ID]@row,[Form Submission Date]:[Form Submission Date], [Form Submission Date]@row - 1)

Michelle St George

I replaced the formula in that one cell with what you put above

Paul Newcome

Ok. Try this…

Clear the formula from that column, save the sheet, log out, clear your cookies and cache, log back in, and try retyping the INDEX/COLLECT from scratch.

We can see by the COUNTIFS that there is definitely at least one row that meets the range / criteria sets, so the INDEX/COLLECT should be working. I wonder if maybe there is just a little bit of latent data being stored on the back-end that just needs cleared out.

rrenee

I was thinking about other ways to do it to mitigate the day before check entirely, and if you're interesting in changing it, you could be try collecting the reading from the row with the first date that is less than the current row using a helper column. That way, it always retrieves the reading difference based on the most recent entry and does not rely on daily submission for the formulas to work. I did a mini-test, and it looks like this:

Most Recent Previous formula, max date less than current row:

=MAX(COLLECT(DATE:DATE, [SITE ID]:[SITE ID], [SITE ID]@row, DATE:DATE, <DATE@row))

Previous Reading formula:

=IF([Most Recent Previous]@row = "", "No Data",

INDEX(COLLECT(Reading:Reading, [SITE ID]:[SITE ID], [SITE ID]@row, DATE:DATE, [Most Recent Previous]@row), 1))

Difference

=IF([Previous Reading]@row = "No Data", 0, Reading@row - [Previous Reading]@row)

If the other method works, nevermind! Just a thought.

Michelle St George

I've been using the Smartsheet App but I opened up a browser and cleared all cache, cookies, etc, logged out, rebooted my laptop and logged back in. I then created a brand new column to try the formula (which I manually typed in) and still see the same error

Michelle St George

@rrenee - Thank you for the suggestion. I am hoping you can see why I'm getting an #UNPARSEABLE error…

My formula (sitting in TEST)

=IF([Most Recent Previous Date]@row="", "No Data", INDEX(COLLECT([Meter Reading (Gallons)][Meter Reading (Gallons)], [Site ID]:[Site ID], [Site ID]@row, [Form Submission Date]:[Form Submission Date], [Most Recent Previous Date]@row),1))

rrenee

It looks like you're missing a semi-colon between [Meter Reading (Gallons)] colon here [Meter Reading (Gallons)] at the beginning of the collect function. Let me know if that works!

Michelle St George

Thank you, that was it. I fixed that and now get #INVALID OPERATION :(

(Secretly I'm happy that I'm not the only one struggling to make this work but in reality I feel like it should be easy since I basically have the same formula working in another cell)

Paul Newcome

There must be something wrong with the [Meter Reading (Gallons)] column then.

Make sure all filters are off. Then create a new filter on that column and start it out where that column "is one of". Then it should give you a dropdown to select from. See if there are any error messages listed in that.

Michelle St George

WINNER WINNER CHICKEN DINNER! @Paul Newcome - That's exactly what it was (error in the Meter Reading (Gallons) column)… I had not consider using a filter to find it before but that is genius. The offending data was entered as text so I converted using VALUE and it all worked.

Many thanks to @Paul Newcome & @rrenee for your help with this!

Paul Newcome

Happy to help. 👍️