Multiple IFERROR Fx with INDEX(COLLECT returning Incorrect Argument Error

Marissa Pettifer

I'm trying to write a formula, referencing a job number, to pull job due date, checking three different databases, our backlog and then shipment summary and then Order Entry Sheet. I have this formula that works when I pull Part Description:

=IFERROR(INDEX(COLLECT({BACKLOG PART DESCRIPTION}, {BACKLOG JOB NUMBER}, [Job Number]@row), 1), IFERROR(INDEX(COLLECT({SHIP PART DESCRIPTION}, {SHIP JOB NUMBER}, [Job Number]@row), 1), INDEX(COLLECT({OE PART DESCRIPTION}, {OE JOB NUMBER}, [Job Number]@row), 1)))

But if I modify it to pull QTY, I get the Incorrect Argument Error code and can't figure out why. This is what the formula looks like if I modify it. Does anyone have any ideas why this isn't working?

=IFERROR(INDEX(COLLECT({BACKLOG QTY}, {BACKLOG JOB NUMBER}, [Job Number]@row), 1), IFERROR(INDEX(COLLECT({SHIP QTY}, {SHIP JOB NUMBER}, [Job Number]@row), 1), INDEX(COLLECT({OE QTY}, {OE JOB NUMBER}, [Job Number]@row), 1)))

Paul Newcome

Is that error present in the QTY column of any of the three sheets even once?

Marissa Pettifer

@Paul Newcome, it is not. The columns on the three sheets are all manual entry databases with stagnant, non-formula values. Also all formatted in the same Text/Number column type.

Paul Newcome

Are you able to provide a screenshot of the formula within a cell?

Marissa Pettifer

Sure. This is a screenshot viewing the column formula for the Job QTY Column, where it's returning the error

And this is the formula that is working, under the part description column.

Just for info, the first three columns are supposed to be throwing up the errors that are there. Their reference cells are currently empty.

Paul Newcome

It looks like it is working there on the row with 164351-05 as well as the row below it. What is in those cells?

Double check your cross sheet references to make sure they are definitely referencing the correct columns.

Marissa Pettifer

It's a column formula, so it's the same for all the rows. Screenshot included just to double check and reference though.

164351-01, 164351-05 & 163729-08 are all present on my backlog, the first reference sheet.

163729-01 pulls from the second reference sheet, the shipment summary, SHIP

And the bottom entry, 163729-03 is only present on the third point of reference, my Order Entry Summary.

I went back through the references & SHIP QTY & OE QTY are definitely referencing the columns I want them to.

Paul Newcome

So it sounds to me like the issue is when you are needing to pull from the second and third sheets. I do notice that they syntax is different from what I would have used. I didn't question it because you said it was working for the other formula, but lets try a different syntax anyway just to see if that helps.

=IFERROR(IFERROR(INDEX/COLLECT({Sheet 1}), 1), INDEX(COLLECT({Sheet 2}), 1)), INDEX(COLLECT({Sheet 3}), 1))

Notice the placement of the IFERROR statements and closing parenthesis. Try that syntax and lets see what happens.

Marissa Pettifer

Huh, that's interesting. I didn't consider that format for it. I think I wrote this formula properly within your context. Looks like it's operating the same way though.

I do have another theory now - I tried continuing my pattern of formulas onto the next column to pull job value, and I get an error that Smartsheet can only reference 100,000 cells in one sheet. So perhaps I hit that limit somewhere inside my SHIP QTY & OE QTY references?

Paul Newcome

It is possible that you just have too much going on, but generally there will be an error that pops up when you hit a limit like that.

What happens if you break each INDEX/COLLECT out into separate cells?

Marissa Pettifer

So actually I deleted the description references off of the sheet to see for the second and third reference sheet to see if it did anything, and it did. The formulas populate for the QTY now. I'm going to need to go a different route for how I handle this data. I'll create separate sheets to reference the second and third sheets and just leave the references for the backlog on this one, use an automation to move the rows to an appropriate sheet.

I appreciate your help trying to work through this though, and I really like your suggestion to isolate that index/collect in its own cell to see how that works to see whether it was the reference or the formula. Next time I run into some fx issues I'll keep that one in mind.