#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!

KimTDSYN

I found that:

1. "index" works with blank values
2. "Iferror" won't work if the type doesn't match the source column type (ie one is date and one is text)
3. "index" works on dates. Just has to be the same type as the source file