MAX Function Error (#INVALID COLUMN VALUE)

Kristopher Penland

Hello,

I am working on creating a project plan. Instead of having the singular Status Update field that comes with the project plan template, I wanted to keep a log of status updates, then reflect the most recent info from that log in my project plan.

I created a new sheet/grid with 3 columns. Weekly Comment, (automatic) Created Date, and (automatic) Created By.

Now, in the project plan I am trying to update the Date of Status Update field to reflect the MAX date from the Created Date column in the new sheet. I hoped to then use the result of that formula to generate the latest status update in the project plan by using an INDEX(MATCH()) function to get the update comment.

However, when I complete the formula I only get back an error, (#INVALID COLUMN VALUE).

Sincerely,

Kris

Mike Wilday

In Fact, that error means that you are trying to put a date formula in non-date column. The column you put that MAX formula has to match the column you're pulling it from. You should make the column a Date column or convert that date to text format. using the following formula... .

=DATEONLY(MAX(Created:Created)) + ""

OR if you want to include the time, then

=MAX(Created:Created) + ""

Leibel S

@Kristopher Penland

Add the below columns to your Weekly Status Sheet.

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: "=MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

In your project plan sheet you can pull the latest status by doing:

=INDEX({Project Status Log Status},MAX({reference to ROW# on your status sheet}),0)

Make sure to fix the Cross Sheet References in the above to reflect Cross Sheet References in your sheet...

Mike Wilday

I would recommend hiding the auto creation date and create a helper column that converts the date/time format of the auto created date to a standard date.

Create a new date column called Created On, and then in the first cell put this formula:

=DATEONLY(Created@row)

Then right click on that link and make the formula a column formula.

Then you can hide the Created Column and use the Max value from your new date only column.

Mike Wilday

In Fact, that error means that you are trying to put a date formula in non-date column. The column you put that MAX formula has to match the column you're pulling it from. You should make the column a Date column or convert that date to text format. using the following formula... .

=DATEONLY(MAX(Created:Created)) + ""

OR if you want to include the time, then

=MAX(Created:Created) + ""

Kristopher Penland

Hi Mike,

Thank you for getting back to me so quickly.

I actually tried your solution before, but forgot to mention it in my initial post. However, in my earlier attempt I didn't include the DATEONLY addition that you included.

I've tried your solution to no avail, I am still getting the same error on my project plan.

Any other ideas?

Sincerely,

Kris

Kristopher Penland

https://community.smartsheet.com/discussion/comment/270538#Comment_270538

Hey again Mike,

This worked to generate the date without an error! However, The INDEX(MATCH()) function will not find a match to get the most recent comment. I think it's because the value from the MAX function is now reflected as a text/number value and it is searching against a date column for a match.

I've tried it several ways, just in case

=INDEX({Project Status Log Status}, MATCH([Developers Notes]17, {Project Status Log Created Date}, 0))

=INDEX({Project Status Log Status}, MATCH([Developers Notes]17, {Project Status Log Created Date} & "", 0))

Any ideas how I can get this to work to find the MATCH?

Sincerely,

Kris

Andrée Starå

Hi @Kristopher Penland

You should use an IINDEX/MAX combination instead.

Did that work/help?

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

Kristopher Penland

https://community.smartsheet.com/discussion/comment/270544#Comment_270544

Interesting idea I hadn't thought of before, Andree.

I tried using an INDEX(MATCH(MAX())) to find the Status Update comment corresponding with the most recent date within the other sheet, but got back an #UNPARSABLE error.

=INDEX({Project Status Log Status}, MATCH(MAX({Project Status Log Created Date}),{Project Status Log Created Date}, 0)))

Any other ideas to get this to work?

Sincerely,

Kris

Andrée Starå

@Kristopher Penland

You'd only use INDEX and MAX, not the MATCH part.

Make sense?

Leibel S

@Kristopher Penland

Add the below columns to your Weekly Status Sheet.

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: "=MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

In your project plan sheet you can pull the latest status by doing:

=INDEX({Project Status Log Status},MAX({reference to ROW# on your status sheet}),0)

Make sure to fix the Cross Sheet References in the above to reflect Cross Sheet References in your sheet...

Kristopher Penland

https://community.smartsheet.com/discussion/comment/270548#Comment_270548

@Andrée Starå . Apologies for the delay in my response. This worked, thank you for the guidance Sir!

Sincerely,

Kris

Andrée Starå

@Kristopher Penland

No worries!

Excellent!

You're more than welcome!

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

Ian Cooper

https://community.smartsheet.com/discussion/comment/270538#Comment_270538

Just wanted to jump in and say thank you. I refuse to tell you how close I was to throwing my laptop. Very very helpful.