=INDEX =MATCH to pull up dates.

Indira Buike

Hello,

I've been wracking my brain trying to understand why this doesn't work.

I have a sheet with some project-related data including the columns "START DATE" and "END DATE" which are date columns because they have dependencies on the duration column etc.

I'm making a new sheet for resource allocation, and I need to bring over those dates.

What I'm trying to do is have each project with their ID, so when I type the ID number data as the project name, status, project leader, and DATES are brought over.

I managed to pull everything with =Index =Match formulas, except for the dates.

=INDEX({Sheet with the column with the dates I need}, MATCH([Project ID]@row, {Sheet with the column with projects IDs that need to be matched}))

this simple formula has worked very effectively, except for the Date column, I created another column named Start Dates and have it in text/number format so I could write the formula, but didn't work either, showing the error #INVALID COLUMN VALUE.

I also tried using

=VLOOKUP([Project ID]@row, {sheet with the range of 1rst column being my projects IDs till my last column being the Start Date, 11, false)

11: is the number of the column where my dates were.

that didn't work either bringing up the same error #INVALID COLUMN VALUE.

What am I doing wrong? or how can I bring up those dates when I type the Project ID?

Thank you!

Andrée Starå

@Indira Buike

Excellent!

Glad we got it working!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

Paul McGuinness

Hello @Indira Buike

Is the column you are pulling the dates to, formatted as a date column?

If pulling data into a column not set up for that data type then it will return a error?

Thanks

Paul

Indira Buike

Hi Paul!

Yes i'm pulling the date from a column that is formatted as a date column, and i'm trying to bring it over to a text/number format column, i also tried in a date format column, but the formula just shows up.

Andrée Starå

Hi @Indira Buike

I hope you're well and safe!

Is it a column used in the Project Settings, and are you using the dependencies feature?

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Brian_Richardson

If you need the date result to be in a text column, you can use a trick. Add +"" to the end of your formula to make the result text rather than a date.

If you want the date result to be in a date column, but you're only seeing the formula when you type it into the date column, that means that column is the Start or Finish date on a Gantt view and is controlled by Smartsheet. You need to either change the setting to use a different column, or put the result in a different date column.

One other tip, add ,0 to the end of your MATCH statement so it searches unsorted. I get better consistency in matches that way. If someone inserts a row or rearranges your lookup data, it won't break if MATCH has ,0 as the sort order.

Indira Buike

Hi Andree,

Yes is a column used in the Project Settings, and I'm using the dependencies feature. Is a sheet to be able to see resource allocation.

Indira Buike

Hi Brian,

I'm trying to use your tips but I can't get it to work.

my formula is =INDEX({Updated project list Start Dates}, MATCH([Project ID]@row, {Updated project list Projects IDs}))

I tried

=INDEX({Updated project list Start Dates}, MATCH([Project ID]@row, {Updated project list Projects IDs}, 0, ""))

but I get the error #INCORRECT ARGUMENT SET

Andrée Starå

@Indira Buike

Try something like this. (two options)

=INDEX({Updated project list Start Dates}, MATCH([Project ID]@row, {Updated project list Projects IDs}, 0)

=IFERROR(INDEX({Updated project list Start Dates}, MATCH([Project ID]@row, {Updated project list Projects IDs}, 0)), "")

Did that work?

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

Indira Buike

Hi @Andrée Starå

I tried both options and I get the same error #INVALID COLUMN VALUE maybe is something about the column properties, the sheet which has the dates im trying to pull is a date column with dependencies, and i'm trying to bring it to a column with text/number properties, does that affect anything?

This is a screenshot of the Sheet

The START DATES column is a text/number column in which I'm trying the formulas, but ideally, it should also be a date property column because I need it for the allocation purpose.

Indira Buike

@Andrée Starå and @Brian_Richardson

I figure it out! turns out the properties of the column i was copying the date had to also be a "Date type".

Now my issue is that I have dependencies enabled for the resource management tool to work.

Is there a way to use formulas on the date columns that have dependencies enabled? or is there a way a can cell link the info from the NEW date column (The one with the formula which pulls out the date from the other sheet) to the date column which has dependency enabled for the resource management tool?

Thank you both for your help!

Brian_Richardson

@Indira Buike for the "" tip I meant something like =INDEX({Updated project list Start Dates}, MATCH([Project ID]@row, {Updated project list Projects IDs}, 0)+""

Adding the +"" to the end of a formula makes the date result a text result instead. But it sounds like you are good with putting the result in a date column, so you shouldn't need that tip.

Unfortunately there's no way to do a formula or an automation to change Start and Finish if you have dependencies enabled. Smartsheet locks it down so they can control it on the backend and manage it with rollups etc.

You can, however, use Bridge to update start dates and durations on dependency-enabled sheets. If you have Bridge, you can use it to read your dates from one sheet and post those dates to another sheet using some criteria to determine which row to update. It might be a little complicated to setup, so it depends on your appetite! If you have Bridge available, there's pretty good training on Smartsheet University to get started. Obviously, you also need to have paid for Bridge as part of the Gold or Platinum Advanced package.

Andrée Starå

@Indira Buike

Excellent!

Glad we got it working!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!