Filter causing Circular Reference Error - Not obvious why?

Question

I have 3 sheets. SheetA and SheetB contain data and a third sheet where I want to merge the contents of the SheetA and SheetB in it.

I type this formula in the result sheet (the 3rd sheet):

={filter(SheetA!A1:A, arrayformula(ISBLANK(A:A)=false)),
filter(SheetB!A1:A, arrayformula(ISBLANK(A:A)=false))}

However, I get circular reference error! Not sure what caused it? Each filter in the above expression works correctly.

Welcome. Your formula should work. Please share a test sheet so as you can be easier helped. — marikamitsos, Oct 23 '20 at 21:09
@marikamitsos, thanks for willing to help. I modified the post with the sheet link. — NoChance, Oct 23 '20 at 21:13
The sheet has just some test data, Column A has some data in sheetA and SheetB - That is all. - I have this address for the sheet itself: docs.google.com/spreadsheets/d/… but I think you won't be able to access it. — NoChance, Oct 23 '20 at 21:20

marikamitsos · Accepted Answer · 2020-10-23 21:46:24Z

2

The issue is that in your sheet Result in cell A1 (that is in column A) you have a formula like

={filter(SheetA!A1:A, arrayformula(ISBLANK(A:A)=false)),
filter(SheetB!A1:A, arrayformula(ISBLANK(A:A)=false))}

Meaning, you get circular reference error because you refer to column A by using ISBLANK(A:A) in your formula.

Your formula will work if you place your formula in any other column in sheet Result BUT A.

answered Oct 23 '20 at 21:46

marikamitsos

11.3k3 gold badges22 silver badges47 bronze badges

Thank you very much for your help. When I moved the formula in the result sheet to another column I got another error but the circular reference issue is gone. Thank you.
– NoChance
Oct 23 '20 at 22:12
1

Thank you for taking the time to accept. Please update your sheet with the new error so we can probably solve that one as well.
– marikamitsos
Oct 23 '20 at 22:19
I appreciate your willing to help. Never mind, I will try on my own. Thanks again.
– NoChance
Oct 23 '20 at 23:23

Add a comment |

1 Answer 1