INDEX using MAX(COLLECT)

Kayla Q

Hi Smartsheet Fam,

I have the below sheet:

I have a second "helper sheet" that looks exactly like this with a rule set up: any time "MVE Increase Recommended" changes to "yes", copy the row to the helper sheet. I have this set up so I can capture a snapshot of the person who updated the "MVE Increase Recommended" cell based on the "Modified By" data and return the "Modified By" value in the "MVE Increase Recommended by" cell.

See here for more details on the helper sheet by @Andrée Starå : Lock or Store Date/Value Solution without using Zapier — Smartsheet Community

I don't understand why I'm getting the #INVALID DATA TYPE error.

This is my function: =IF([MVE Increase Recommended]@row = "yes", INDEX({Modified by}, (MAX(COLLECT({Modified}, {KLG Matter Number}, [KLG Matter Number]@row), 0))), "")

The data I'm asking it to return is the "modified by" data, which I'm assuming is either text/number or contact. I've changed the column type in the "MVE Increase Recommended by" column to both of these types, and I'm still receiving the same error. I've tried changing the column type for the "MVE Increase Recommended by" column to every single column type, but I'm still getting the same error. Is the problem that the formula contains a reference to a date field?

Bottom line: I need to return the individual's name in the modified field based on the most recent modified date in the helper sheet.@Andrée Starå

Jason Tarpinian

Your (MAX(COLLECT({Modified}, {KLG Matter Number}, [KLG Matter Number]@row), 0))) part of the formula is the row you want to return. You have it set up to return {Modified}, which seems to be a date (#INVALID DATA TYPE). Do you have a Auto-number column on the other sheet? You should be able to replace {Modified} with {Row #} and it should be back up and running for you.

Kayla Q

@Jason Tarpinian I just implemented this suggestion. Thanks!

I changed the formula to this:

=IF([MVE Increase Recommended]@row = "yes", INDEX({Modified by}, (MAX(COLLECT({Helper ID}, {KLG Matter Number}, [KLG Matter Number]@row), 0))), "")

Then I got an #INCORRECT ARGUMENT SET error.

I switched the placement of the 0 at the end, but I'm still getting the same error.

Newest iteration of the formula:

=IF([MVE Increase Recommended]@row = "yes", INDEX({Modified by}, (MAX(COLLECT({Helper ID}, {KLG Matter Number}, [KLG Matter Number]@row))), 0), "")

Jason Tarpinian

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc--incorrect-argument-set

Your formula looks correct, so per the INCORRECT ARGUMENT error message notes, double check your cross-sheet ranges of the COLLECT are the same size.

Kayla Q

@Jason Tarpinian I just opened the sheet back up and the error has changed to #INVALID COLUMN VALUE.

I have no idea what would have caused it to change, as I haven't touched it or the helper sheet since my last update yesterday.

My current column type is text/number, and I'm trying to get this to return the "modified by" data, so that should be text.

I'm so confused :(

Maybe I just need to call Support?

Jason Tarpinian

Unfortunately without being able to click around in your solution, I'm not sure now what would be giving you that error again. I worked up a quick test just to make sure the formula syntax is correct, and it appears so.

The couple things I did were:

• "Modified" column is just a simple column formula of =[Modified By]@row, otherwise I notice on the COPY sheet the "Modified By" column will sometimes show automation@smartsheet.com.
• My "Row ID" lookup column on the copy sheet is just an auto-numbered column starting at 1.
• "KLG Matter Number" is a Text/Number type column on each sheet
• My "Modified" column on the COPY sheet is a Contact List type column, and even though I am INDEXing it to a text/number column on my original sheet, it still comes through fine.

=IF([MVE Increase Recommended]@row = "Yes", INDEX({Modified By}, MAX(COLLECT({Row ID}, {KLG Matter Number}, [KLG Matter Number]@row))), "")

Kayla Q

@Jason Tarpinian I really appreciate your extra help on this.

This is one of the most incredibly frustrating Smartsheet experiences I've had. I'm still getting the #INVALID COLUMN VALUE error. I'm following all of your directions to a T. My target column is a text/number type and every single column I'm referencing is a text/number type. Under these conditions, there is literally no way possible I should be getting an #INVALID COLUMN VALUE error.

For funsies, I changed the "modified by" column in the COPY sheet to a contact column and also changed the "MVE increase recommended by" column to a contact column. With that, I get the #CONTACT EXPECTED error. It makes no sense.

@Andrée Starå, since this is your solution (Lock or Store Date/Value Solution without using Zapier — Smartsheet Community), do you happen to know why I'm experiencing these problems?

Kelly Moore

Hey @Kayla Q

I was able to replicate the error #INVALID COLUMN VALUE when the equivalent of your [KLM Matter Number]@row was not a match to the data. For trouble shooting purposes, what happens if you remove the Max/Collect portion of the formula and just hard code in a value that would yield a valid response in the formula. If this work, piece the Collect apart, one term at a time, until you find the culprit that is causing problems.

Kayla Q

@Kelly Moore I'm struggling with this a little bit. It could be that I don't really understand how all of these functions work. If I use INDEX(MATCH), I actually do get the desired result:

=INDEX({Modified By}, MATCH([KLG Matter Number]@row, {KLG Matter Number}, 0))

Kayla Q

Another odd thing, if I just do this:

=MAX(COLLECT({Helper ID 2}, {KLG Matter Number}, [KLG Matter Number]@row))

Then I get a "0" in return:

Kelly Moore

Hey

Do you need a Collect or will the Index/Match work for you? The MATCH function provides the item position in the list - which is what your Row ID helper column is trying to provide.

If you need the Index/Collect then continue trouble shooting. Based on the Index/Match working, the problem seems to be with the {Row ID} range. (It's the only range not also in the working Index/Match formula)

Using Jason's formula

=IF([MVE Increase Recommended]@row = "Yes", INDEX({Modified By}, MAX(COLLECT({Row ID}, {KLG Matter Number}, [KLG Matter Number]@row))), "")

go into the formula and completely delete the {Row ID} range from the formula. When you do that, the formula window will show the REFERENCE ANOTHER SHEET button again. Click that and re-insert your Row ID range. It's easy to have made a mistake when inserting cross sheet references.

What happens after you re-insert the {Row ID} into the formula?

Kelly

Kayla Q

Hi @Kelly Moore,

Unfortunately, I have to take into consideration that there could be several of the same KLG Matter Number, so I need to bring back the largest Row ID from the other sheet. The other sheet has an auto-numbering function, so each row that is added will receive a unique Row ID.

Are you suggesting that I use a "1" instead of a "0" in the index match to bring back the item in a sorted descending fashion? I suppose that could work if I ensure the other sheet is sorted correctly and that no one will touch it and mess with the sorting. That makes me just a little nervous, but it's an alternative!

If I reinsert the Row ID, I still get the same #INVALID COLUMN VALUE error 😔

I have a Pro Desk session tomorrow on another topic, so maybe I can squeeze this in.

Kelly Moore

Kayla

Oh, I didn't see you had reinserted the range.

Kelly Moore

For kicks, try this

=IF([MVE Increase Recommended]@row = "Yes", INDEX({Modified By}, VALUE(MAX(COLLECT({Row ID}, {KLG Matter Number}, [KLG Matter Number]@row)))), "")

If that doesn't do anything, move the VALUE in between the Max and Collect

Oh, and I had another thought

=IF([MVE Increase Recommended]@row = "Yes", INDEX({Modified By}, MAX(COLLECT({Row ID},{Row ID}, ISNUMBER(@cell), {KLG Matter Number}, [KLG Matter Number]@row))), "")

Kelly

Kayla Q

@Kelly Moore your first solution yields the error #INVALID COLUMN VALUE. I triple checked all of the column references and they are correct, so I'm not sure why this is happening.

The second solution yields the error #INVALID DATA TYPE.

Honestly, I think I'm just going to give up. The whole idea of this is that I have a stakeholder who wants to see who clicked a checkbox. I'm just going to tell her that she will have to right-click and select "view cell history" to achieve this. It's not worth the hours of work I've put into it.

I really appreciate all of your assistance!

Kelly Moore

Yes, without seeing the data, I'm not sure what to tell you. The other number fields in the formula may also be a mismatch of numbers and text. You could play with the VALUE function with them. If you want another look, is it possible for you to share the sheets with me? Or zoom.

Kelly