#INVALID COLUMN VALUE error

Raquel

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))), "")

KPH

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))), "")), "")

KPH

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

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

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

@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

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

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

KPH

Wonderful!