#INVALID COLUMN VALUE error

Options

Hello Community,

I'm trying to combine two formulas but keep getting an error. The first formula sums children if it's a parent row or leaves the cell blank. The second look for the most recent data in a row (I have month in the columns from July to June). They work independently but when I combine them I keep getting an #INVALID COLUMN VALUE error. Wrapping the formula in an IFERROR formula doesn't help either. Any suggestions?

=IF(COUNT(ANCESTORS($[Strategic Plan Tactics & Supporting Projects]@row)) < 1, SUM(CHILDREN()), "")

=INDEX(July@row:June@row, 1, COUNT(July@row:June@row))

Combined:

=IFERROR(IF(COUNT(ANCESTORS($[Strategic Plan Tactics & Supporting Projects]@row)) < 1, SUM(CHILDREN()), INDEX(July@row:June@row, 1, COUNT(July@row:June@row))), "")


Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    Ah! That explains it. I thought the formula wasn't working at all.

    The INDEX part won't work if there is no data in the row. Adding another part to the formula to return a blank if the total of the July-June columns is not more than 0 will solve that. The bold parts here:

    =IFERROR(IF(COUNT(ANCESTORS($[Strategic Plan Tactics & Supporting Projects]@row)) < 1, SUM(CHILDREN()), IF(SUM(July@row:June@row) > 0, (INDEX(July@row:June@row, 1, COUNT(July@row:June@row))), "")), "")

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Hi @Raquel

    The formula looks good and works on my test sheet (with or without the IFERROR). Is it possible that one of your columns is not a text/number column?

  • Raquel
    Raquel ✭✭✭✭
    Options

    Hi @KPH

    Thanks for looking into this. All of my columns are text/number so not sure what the issue is. I might just need to have different formula in different rows as a workaround.

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Hi

    It's a strange one. Is it just one row that causes a problem?

    Here it is working for me (maybe you can spot a difference - I can't)

    (I removed the $ so I could do this as a column formula but it works with or without it)

  • Raquel
    Raquel ✭✭✭✭
    Options

    @KPH It's only a problem when there's no data in the row. So if the row is blank because there isn't any data yet, it shows the error.

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    Ah! That explains it. I thought the formula wasn't working at all.

    The INDEX part won't work if there is no data in the row. Adding another part to the formula to return a blank if the total of the July-June columns is not more than 0 will solve that. The bold parts here:

    =IFERROR(IF(COUNT(ANCESTORS($[Strategic Plan Tactics & Supporting Projects]@row)) < 1, SUM(CHILDREN()), IF(SUM(July@row:June@row) > 0, (INDEX(July@row:June@row, 1, COUNT(July@row:June@row))), "")), "")

  • Raquel
    Raquel ✭✭✭✭
    Options

    @KPH It worked, you're a life saver! Thank you so much for your help!!

  • KPH
    KPH ✭✭✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!