INVALID COLUMN VALUE

Jeana · 12/06/20

Can't figure out why I'm getting this when none of the Children for Deliverables and Tasks are checked off in the checkbox column. Works fine as long as one of them is checked.

Screen Shot 2020-12-06 at 10.54.36 AM.png

=IFERROR(INDEX(CHILDREN([Deliverables and Tasks]@row), COUNTIFS(CHILDREN([Ready to Hand-Off to Next Task Owner]@row), 1)), "")

Thanks!

Genevieve P. · 12/10/20

Hi @Jeana

It looks like the INDEX is having a hard time when the COUNTIFS returns 0. We can add an IF statement at the front to say that if no boxes are checked, return a blank cell... otherwise, perform your INDEX statment.

Try this:

=IF(COUNTIFS(CHILDREN([Ready to Hand-Off to Next Task Owner]@row), 1) = 0, "", INDEX(CHILDREN([Deliverables and Tasks]@row), COUNTIFS(CHILDREN([Ready to Hand-Off to Next Task Owner]@row), 1)))

Cheers!

Genevieve

Jeana · 12/06/20

I think it's because I"m referencing a checkbox column. Do I just need a helper column to translate the checkbox to a "yes" or "no" and reference that in the formula? This works but is there a better way?

Jeana

David Joyeuse · 12/07/20

Hi @Jeana

I think the problem comes from how the INDEX formula works.

If I understand how you set it up, if no checkbox is marked, then your COUNTIF returns 0 as the lin in your CHILDREN range which starts at 1 I believe.

Maybe you could try to rewrite your formula using:

=INDEX(COLLECT(CHILDREN([Deliverables and Tasks]@row), CHILDREN([Ready to Hand-Off to Next Task Owner]@row), 1),1)

Should do it.

Jeana · 12/07/20

Thanks David but now I'm getting #INVALID VALUE error if I don't have any of the boxes checked for the CHILDREN. Hhhhmmmmm....

Jeana

Jeana · 12/07/20

What I am wanting is to check the CHILDREN in the column "Deliverables and Tasks" and if the CHILD row has a check mark for "Ready to Hand-off to Next Task Owner " then display the value for Deliverables and Tasks for the row where the box is checked. This is returning the value correctly when a box is checked using this formula:

=IFERROR(INDEX(CHILDREN([Deliverables and Tasks]@row), COUNTIFS(CHILDREN([Ready to Hand-Off to Next Task Owner]@row), 1)), "")

Screen Shot 2020-12-07 at 12.23.59 PM.png

The issue is that when NO boxes are checked it shows the error message. I want it to be blank if no boxes or checked.

Screen Shot 2020-12-07 at 12.24.11 PM.png

Any ideas would be greatly appreciated.

Jeana

Genevieve P. · 12/10/20

Hi @Jeana

It looks like the INDEX is having a hard time when the COUNTIFS returns 0. We can add an IF statement at the front to say that if no boxes are checked, return a blank cell... otherwise, perform your INDEX statment.

Try this:

=IF(COUNTIFS(CHILDREN([Ready to Hand-Off to Next Task Owner]@row), 1) = 0, "", INDEX(CHILDREN([Deliverables and Tasks]@row), COUNTIFS(CHILDREN([Ready to Hand-Off to Next Task Owner]@row), 1)))

Cheers!

Genevieve

Jeana · 12/10/20

@Genevieve P

Perfect! Thanks so much Genevieve!

Cesar_Rodriguez · 09/27/24

@Genevieve P. I have a different issue, I have a formula with a dates that is giving me the error #INVALID COLUMN VALUE and I´ve tried what you were suggested above to fix it but didn´t work, could you please help me to understand what could be the possible issue on this? Thank you so much.

=TODAY() - MAX([Last Service Data GMM]@row, [Last Service Data MMM]@row)

Genevieve P. · 09/27/24

Hi @Cesar_Rodriguez

The two values you're referencing are both blank, so the MAX function won't be able to find the MAX between them. I would but an IF statement in front that = 0 if the cell is blank:

=TODAY() - MAX(IF[Last Service Data GMM]@row = "", 0, [Last Service Data GMM]@row), IF[Last Service Data MMM]@row = "", 0, [Last Service Data MMM]@row))

Cheers,

Genevieve

Cesar_Rodriguez · 09/27/24

#INCORRECT ARGUMENT SET is what SS shows with the proposed formula @Genevieve, any idea why this is happening? Thanks

=TODAY() - MAX(IF([Last Service Data GMM]@row = "", 0, [Last Service Data GMM]@row), IF([Last Service Data MMM]@row, "", 0, [Last Service Data MMM]@row))

😕

Genevieve P. · 09/27/24

Hi @Cesar_Rodriguez

The second IF statement is missing a = sign instead of a comma:

=TODAY() - MAX(IF([Last Service Data GMM]@row = "", 0, [Last Service Data GMM]@row), IF([Last Service Data MMM]@row ="", 0, [Last Service Data MMM]@row))

Cheers!
Genevieve

Cesar_Rodriguez · 09/27/24

I made the change but it is still showing the same. 😔

=TODAY() - MAX(IF([Last Service Data GMM]@row, ="", 0, [Last Service Data GMM]@row), IF([Last Service Data MMM]@row, ="", 0, [Last Service Data MMM]@row))

Genevieve P. · 09/30/24

Hi @Cesar_Rodriguez

The = sign replaces a comma 🙂

Try copy/pasting this version where I've edited out your extra commas:

=TODAY() - MAX(IF([Last Service Data GMM]@row ="", 0, [Last Service Data GMM]@row), IF([Last Service Data MMM]@row ="", 0, [Last Service Data MMM]@row))

Cheers,
Genevieve

Cesar_Rodriguez · 09/30/24

It has changed again showing the same error that in the beginning, this is killing me.

Cesar

Genevieve P. · 09/30/24

Hey @Cesar_Rodriguez

My apologies, I think I misunderstood how your sheet was set up. I can see here that you're not subtracting a number off of Today, but instead a date.

Today - 0 is a Date, which is why you're getting the error.

Since you sometimes have one cell blank, we don't want to replace the 0 with TODAY() otherwise it will make any blank cell the "max". Instead we want the output to be 0 if both cells are blank.

Try:

=IF(AND([Last Service Data GMM]@row ="", [Last Service Data MMM]@row =""), 0, TODAY() - MAX([Last Service Data GMM]@row, [Last Service Data MMM]@row)

Cheers,
Genevieve

Cesar_Rodriguez · 09/30/24

This does work! I truly appreciate your help @Genevieve P. thanks a lot!

Cesar

Formulas and Functions

Formulas and Functions

INVALID COLUMN VALUE

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Counting occurrences of a string in a row

Rounding to nearest Thursday in the past or current day if it's Thursday

JOIN function in a Sheet Summary