Array formulas and imported queries

[TyeReece]

I have a dashboard works now with tables, ranges etc. However, I want to improve it by linking excel tables to access queries (as opposed to copying and pasting). However, when I do this the array formulas I use in the dashboard stop working. Do array formulas not work with tables made from imported queries?

 

[Fazza]

Array formulas should be fine. Be sure the expected cells are being correctly referenced after the queries are refreshed.

Also, maybe with a little more development, you may find that with different queries you can eliminate the array formulas. May not suit you. Depends...

 

[TyeReece]

Thanks. I'll keep at it. I would love to get rid of the arrays but I am not there yet.

 

[Fazza]

I don't know if you sorted out the problem. And I don't know exactly what it is the problem: just that the array formulas "stop working".

What I guess happens, is the formulas references a range of cells, say A2:A10000, that are the query results. When the query refreshes, the old cells are replaced by the refreshed results and along the way the "A2:A10000" cells disappear and the formula will now have a REF error. If that is the case, can you address? One way without code might be to use defined names.