Google user

Original Poster

Feb 12, 2021

Query returns #N/A when sheet is opened - copy&pasting again fixes it. Automatic solution?

Hello fellow googlers,

I have a formula that is returning me a list of items that is contained in a list.

UNIQUE(QUERY('PN ID Match'!A2:B,"SELECT A,B WHERE B matches '"& TEXT(TEXTJOIN("|",1,FILTER(W3:W30,W3:W30<>"",W3:W30<>0)),0)&"' ",0))

It also works and is returning information. However as soon as I close and open it the Query returns an empty output. This can be fixed by deleting the formula and inserting it again. But I need this to work automatically but I cant find a solution around it. Also putting Formula x into IFERROR(x,x) does not fix it. It still returns an empty output.

Any ideas on how to solve it?

Details

Sheets,Microsoft Windows,Business

Locked

Informational notification.

This question is locked and replying has been disabled.

Community content may not be verified or up-to-date. Learn more.

Recommended Answer

Feb 15, 2021

Hey Gill,

thanks for comming back to this question.

I created this sample sheet. The formula can be found in Extract 1!X3

https://docs.google.com/spreadsheets/d/1X78JPGYuJ5kJ1K6NVb6535tL-MPdyP7wiEVQEGO2j_A/edit#gid=1097598075

Best,

Marcel

Diamond Product Expert Gill K recommended this

Gill K

Diamond Product Expert

Feb 15, 2021

Marcel,

I haven't fully traced the logic flow yet, but I think that the issue is a circular dependency, and given that you force recalculations on the hour, as well as with changes, sometimes some of your formulas are returning values, and other times they aren't. Which you know, of course!

I can't see any reason why you should need the reiterative setting turned on, and I'll keep looking to see what the flow is, and if we can correct this.

Cheers,

Gill

When you've received a response that answers your question, please observe these forum courtesies;

• Leave your demo sheet shared as part of this solution's archive,

• Click Recommend on the post that best addressed your question, and

• Post again soon!

Gill K

Diamond Product Expert

Feb 15, 2021

Marcel,

Can you test some things for me?

Turn off the "Iterative calculation" in Spreadsheet settings.

On the Analysis page, toggle the "Search for ID 2" tickbox on and off, and observe the results.

Do they make sense to you?

When "Search for ID 2" is turned OFF, in Extract 1, V3 (ID Supplier) shows the value from Analysis C3, 10017635. It feels like it should be the other way around, but that could be that I don't understand the logic regarding ID Supplier.

Your formula in Extract 1 V3 is perhaps conterintuitive:

=IF(Analysis!$H$8 = false, IF(Analysis!C3 = "",0,Analysis!C3),"")

Your condition, Analysis!$H$8 = false, will be TRUE when it is NOT CHECKED. I would tend to write it as:

=IF(Analysis!$H$8, action if TRUE, action if FALSE)

=IF(Analysis!$H$8 = TRUE, action if TRUE, action if FALSE)

Either method works, of course, but I was confused by your test for false.

Having said all this, with the Search for ID2 checked, and Iterative calculations turned off, is the sheet behaving the way you expect?

Let me know.

Cheers,

Gill

When you've received a response that answers your question, please observe these forum courtesies;

• Leave your demo sheet shared as part of this solution's archive,

• Click Recommend on the post that best addressed your question, and

• Post again soon!

Google user

Original Poster

Feb 17, 2021

Thanks for taking the time, I will try it this way!

Google user

Original Poster

Feb 17, 2021

So I made the changes to the IF(...,true,false) and thanks for pointing this out. It definitely does make more sense this way.

So maybe to give you some background information. We have an internal article id and our supplier has his article ids. However the supplier has multiple IDs for the same article. Now when our team should look up this information, we want to make sure that we are considering every information on every article. So if we are looking up their ID, we check which articles these are in our assortment and then do another backwards check to cover every ID. IF we look up our IDs we solely do the backwards check.

Now - ticking it on and off produces the expected results. For this Sheet this would be totally fine as a workaround however we have another one that uses basically the same formula. This is not actively used but is extracting data and should be able to run in the background and will cause errors. So if there is no fix for this issue, I guess I would need to use another formula where hopefully no error occurs?

Google user

Original Poster

Feb 17, 2021

So I did some changes to the formulas now. I stopped using 'matches' and replaced it with the usual ' = ' combined with a join instead of the Textjoin used previously. This seems to do the trick and the Formulas work already when I open the Spreadsheet.I hope that this will also be aplicable to the other sheet. I will keep you posted. Thanks a lot so far for your help Gill!

Gill K

Diamond Product Expert

Feb 17, 2021

Thanks for the update, Marcel, and I'm glad things are better.

Let me know if you still have any issues, either with the current sheet, or the second one you are referring to.

Cheers,

Gill

When you've received a response that answers your question, please observe these forum courtesies;

• Leave your demo sheet shared as part of this solution's archive,

• Click Recommend on the post that best addressed your question, and

• Post again soon!