Updating Date column with Date Column in another sheet with a condition

PJMCM · 08/27/24

I have two sheets with a lot of columns. Both sheets contain a Docket No. and a Hearing Date Column. I am trying to have the Sheet 2 look at the Docket No. in Sheet 1 and if the Docket No. is the same to update the Hearing Date Column in Sheet 2 with what is entered in Sheet 1 and always stay updating should there be a change to the date in sheet 1.

Any ideas?

Shanky Paul · 08/27/24

You can use the Index (Match) formula to achieve this.

Open Sheet 2 (where you want the Hearing Date to update automatically).

Select the Hearing Date Column in Sheet 2.

Insert the following formula in the first row of the Hearing Date column (or wherever appropriate):

=INDEX({Sheet1 Hearing Date}, MATCH([Docket No.]@row, {Sheet1 Docket No.}, 0))

PJMCM · 08/28/24

Thank you so much for the quick response.

Unfortunately it keeps coming back unparseable. Any idea where i went wrong? The Sheet 1 is Active Dockets

=INDEX([Active Dockets Hearing Date], MATCH([Docket Number]@row, [Active Dockets Docket Number], 0))

Andrée Starå · 08/28/24

https://community.smartsheet.com/discussion/comment/451454#Comment_451454

Hi,

I hope you're well and safe!

You have to create the references. If you look at Paul's example, it has curly brackets {} indicating a cross-sheet range.

Make sense?

I hope that helps!

Be safe, and have a fantastic day!

Best,

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

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

PJMCM · 08/28/24

ok. i realized the docket column in sheet 2 had a different name so changed them both to Docket. I have the brackets correct and no longer get the unparseable but now get an invalid REF on the [Docket]@row. Could the issue be if the column is a primary column in one of the sheets?

=INDEX({Active Dockets Range 1 Hearing Date}, MATCH([Docket]@row, {Active Dockets Range 1 Docket}, 0))

Updating Date column with Date Column in another sheet with a condition

Answers

Help Article Resources

Categories