Index Collect Looking at Auto Number Column

Ron Anderson

I have an index collect statement in one sheet that uses a column with an auto number value in another table. I get an invalid value error with the formula. However, if I change the column type to text/number from auto number it works.

Genevieve P.

Hi @Ron Anderson

I was able to replicate what you're seeing! If your referenced column is being recognized as text, you can adjust your formula to find the value like so:

=INDEX(COLLECT({Address}, {internal id}, VALUE(@cell) = [Property ID2]@row), 1)

This converts the number in the Internal ID column to be a Value then compares it to the Property ID2 column. Let me know if this works for you!

Cheers,

Genevieve

Andrée Starå

Hi @Ron Anderson

I hope you're well and safe!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

What exactly is your existing INDEX/COLLECT formula?

Ron Anderson

=INDEX(COLLECT({Address}, {internal id}, [Property ID2]@row), 1)

The formula works when the value in internal id is a numeric value entered by me. (text/number). As soon as I change it to auto number it fails.

Andre. Sharing sheets with you.

Genevieve P.

Hi @Ron Anderson

The Invalid Value error is indicating that the formula is unable to find a match between your two columns. Is your Property ID2 column a Text/Number or an Auto-Number? I would expect that the two columns will need to be the same type.

Or, if you are only using numbers in your Auto-Number column, they could be seen as text. Try adding "" to your Property ID2 cell, like so:

=INDEX(COLLECT({Address}, {internal id}, [Property ID2]@row + ""), 1)

If that doesn't work, it could be that the value in your Property ID2 column is being seen as text, try adding VALUE around it:

=INDEX(COLLECT({Address}, {internal id}, VALUE([Property ID2]@row)), 1)

Let us know if any of this worked!

Cheers,

Genevieve

Ron Anderson

I tried =INDEX(COLLECT({Address}, VALUE({internal id}), [Property ID2]@row + ""), 1)

and INDEX(COLLECT({Address}, {internal id}, [Property ID2]@row + ""), 1)

and INDEX(COLLECT({Address}, {internal id}, value([Property ID2]@row)), 1)

none work.

You are correct. I think the auto number field [internal id] is text and the value in Property ID2 is a number which is causing the issue. Any thoughts?

Genevieve P.

Hi @Ron Anderson

Are you able to post screen captures of these two columns? (Blocking any sensitive data). I can't seem to replicate the error. It would be helpful to also see a screen capture of the Column Properties for your Auto-Number column.

Thanks!

Ron Anderson

I can share both sheets. Nothing sensitive in them. Send email

Genevieve P.

Hi @Ron Anderson

I was able to replicate what you're seeing! If your referenced column is being recognized as text, you can adjust your formula to find the value like so:

=INDEX(COLLECT({Address}, {internal id}, VALUE(@cell) = [Property ID2]@row), 1)

This converts the number in the Internal ID column to be a Value then compares it to the Property ID2 column. Let me know if this works for you!

Cheers,

Genevieve

Ron Anderson

awsome. that fixed it. Thanks

Genevieve P.

No problem! I'm glad we could figure it out.