INDEX(COLLECT - #Invalid Value Error

Mr. Z

Good afternoon everyone! Looking for some help with INDEX(Collect. I have a main sheet set up and also have created a helper sheet. Once the install date on the main sheet is changed, automation copies that row to the helper sheet.

I want to see on my main sheet the previous install date as well. This I've figured out. I used Index(Collect and have it looking at the 2nd newest entry on my helper sheet with a formula like this.

=INDEX(COLLECT({Install Date - Helper}, {Ticket # - Helper}, [CW Ticket]@row), 2)

Problem is that until there are two date entries into Install Date the Previous Install Date cell shows #INVALID VALUE as see below. Once there are two entries and two rows copied to my helper sheet this goes away.

Seeing all of the #INVALID VALUES makes it hard to read and navigate.

Looking for a way to possibly use an If statement to be able to return a blank cell until the second Install Date change that will then return back the actual historical date.

prime_nathaniel

Mr. Z

iferror
or
ifblank

both should work fine

Mr. Z

Thank you! I have able to get iferror to work