#INVALID VALUE Error

Kevin Rice

I've gotten this formula to work on other sheets but for some reason this one will not accept the formula. The columns where the value is coming from and where I want it to populate are both Text/Number. I've wrapped the formula in an IFERROR and I've also had instances where I had to wrap the whole thing in a VALUE formula if it tries to return it as a string. Neither have been a successful solution to this issue. Below is a snap of the formula as well as the information it is referencing. I've had to re-name some stuff as I've been trialing this formula and troubleshooting but it is referencing the {Date} column in the second image as well as the {Time2} column. There are dates below the yellow box, they're in white font color so they aren't visible.

Bassam Khalil

Hi @Kevin Rice

Hope you are fine if it's possible to create a workspace and save a copy of the two sheets with sample data and share me as admin on that workspace i will create the exact formula for you.

My Email: Bassam.k@mobilproject.it

Genevieve P.

Hi @Kevin Rice

I see that the sheet you're referencing has a lot of formulas in it as well. Is it possible that one of the columns you're referencing has one cell with the #INVALID VALUE error?

If there's one cell with an error your cross-sheet references will then ignore all the other values and simply return an error in this formula as well... even if your current formula is built correctly.

Can you check through the {Feeder Belt Free1}, {Date4} and {Time2} ranges to see if there's an error in the source? Are you using formulas in any of these three columns, and if so, are they referencing another column that could have the error?

Another thing to check is to make sure that your TIME column and your DATE column are both set as date-type of columns (so the Collect function can search for the Time value as a Date in the other sheet).

Let me know if any of this has helped!

Cheers,

Genevieve

Kevin Rice

@Genevieve P I believe the columns I am referencing do have an #INVALID VALUE error; however, I have that formula wrapped in an IFERROR if that makes a difference. I checked the parent source of all of this information and I didn't see any errors in that sheet.

The TIME column (In both sheets) is set up as a Text/Number column. The DATE column is set up as a Date in both sheets.

Genevieve P.

Hi @Kevin Rice

You're right, if the other formulas have an IFERROR around them then this shouldn't return an error. However even if one cell in that column range doesn't have the IFERROR and is showing the error, this can create a domino effect and roll up to your cross-sheet formula.

It doesn't look like you're using column formulas... is it possible that the IFERROR isn't set for the entire column, and one cell is showing this error?

You should be able to use the find toolbar (cntrl + F or command + F) to look for "invalid" and it will bring you to any cells that display this error.

Kevin Rice

@Genevieve P I found another way to format the sheet that gave me what I wanted. Now I'm on to another sheet that is acting funny. I'm getting an #INCORRECT ARGUMENT SET error and the formula is copied and pasted but collecting from a different column. The range sizes are the same and there are no missing arguments from the formula because it is working perfectly right above where I pasted it. I can't figure out what I've done here.

This one works fine.

This one does not. Only change is the column I want it to collect data from.

Genevieve P.

Hi @Kevin Rice

Since the formula structure is the exact same and it's working correctly in the other formula, that once again indicates that the issue may lie with the referenced column, not the formula itself.

What is the {Kettle 3 Stack} range? Is it possible that the range was selected incorrectly? If not, is it possible that there's an #INCORRECT ARGUMENT SET error in this column?

Kevin Rice

@Genevieve P it started working??? I left it alone for a while then refreshed the page and the formulas generated the numbers I was expecting. I didn't alter anything on either sheet during the waiting period either.

Regardless, it's working now! Thanks for all your help!

Genevieve P.

No problem!

Sometimes if there's multiple cross-sheet ranges it can take a bit of time for the initial information to pull through. I find if I've just copied/pasted a formula sometimes drag-filling it over or down then back again causes it to re-fresh and work as expected.

In any case, I'm glad it's working!