Index Collect Formula for Dates resulting in Invalid Value Error

Molly H

Good afternoon,

I am trying to use an Index Collect formula to collect dates that have dependencies. Here is the formula I'm using:

=INDEX(COLLECT({Portfolio Summary | Actual Start}, {Portfolio Summary | Project Number}, [Project Number]@row), 1) + ""

I can confirm that the formula cell is a date column, as well as the referenced column in the portfolio summary. It is also worth noting that the original dates have dependencies turned on. The result is an Invalid Value error.

Any help would be greatly appreciated!

Paul Newcome

As a quick test, what do you get when you enter:

=COUNTIFS({Portfolio Summary | Project Number}, [Project Number]@row)

Molly H

Hi Paul,

Mostly zeros. I do have an occasional 1. There's a complicated path of cell links that I suspect may be the issue, allow me to try to explain:

The origin of the date: timeline with dependencies -> cell link to metadata -> metadata converts to text -> cell link to Portfolio Summary as a date -> cell link to my new sheet as a date. Not all links from metadata are converted to text, but due to the control center blueprint, all new metadata sheets will convert the dates to text.

I really hope this makes sense...

Paul Newcome

Does the INDEX/COLLECT work on the rows where the above test outputs 1? Are there rows outputting a zero that should be outputting a one?

Molly H

Yes to both questions.

Paul Newcome

Ok. Next question... Do you have mixed data types in the [Project Number] column? Do some have leading zeros and others do not and/or do have some letters and others do not?

Molly H

Ah, here's another situation. The original number column is letters and numbers. The [Project Number] column is a RIGHT formula that pulls only the four numbers from the original column. In certain circumstances I have had to create a helper column that pulls the VALUE from the [Project Number] column to get other cell reference formulas to work...

Paul Newcome

So that's the Project Number in the source sheet. What about how the project number is populated in this target/formula sheet?

Molly H

The target sheet is the source sheet.

Context: The goal is to create a Super Gantt showing pipeline dates (Source Sheet) and active project dates (Portfolio Summary). The source sheet feeds into the Portfolio Summary via Control Center cell links, but I still need those active dates brought back to the source sheet for the super gantt.

Paul Newcome

So the data you are indexing is coming from the same sheet the INDEX formula is on? I saw cross sheet references and thought we were working on two separate sheets.

Molly H

I think I may have misunderstood "target sheet". I am pulling dates from the Portfolio Summary onto the Source Sheet.

Paul Newcome

Ok. Sorry about that. just a misunderstanding in terms. When I say "Source Sheet" I mean the sheet that the data we are referencing lives in. When I say "Target Sheet" I mean the sheet we are putting this formula in.

So how is the Project number being populated exactly in each of the two sheets?

Molly H

Ah, and I am using the terms backwards. So going forward the "source sheet" is the portfolio summary. The "target sheet" is the sheet I am building the formulas in.

SO: the project number in the source sheet is a four digit number, no zeros, no letters. It's the same case for the target sheet, four digit number, no zeros, no letters.

Paul Newcome

Ok. So in another comment you mentioned both a RIGHT and a VALUE function. How exactly is it being populated on each of the sheets? Formulas (if so can you provide) or manual entry?

Molly H

Yes, we have a Salesforce Connector that populates the target sheet. The project number populates in the target sheet with EST- before every project numbers so I have a Project Number column with a RIGHT formula to drop the EST-. In past formulas that reference this Project Number column have not worked, so I created a helper column that pulls the VALUE from the Project Number column. The Source sheet Project Number Column is populated via Cell Link, and only has numbers in it.

Screen shots of the Target sheet RIGHT and VALUE formulas:

Paul Newcome

Ok. And the origin of the cell link... How is that original cell being populated?

Here's some more details on why I am asking... You cannot get a match on a text string when looking through numerical values (or the other way round). The reason you were having trouble in other formulas and what I suspect may be the issue here is that the RIGHT function will output a text string, but the VALUE function will output a number value.

Depending on the data type(s) of what you are matching which is based on exactly how something is populated, you may need to reference the helper column if the regular column isn't working (or the regular column if the helper isn't working) for this formula.