How to avoid indirect volatile function

Hi,

as i started to read about volatile functions i found out that my workbook is using too many indirect functions.
So it can cause bad overall performance and i always want to have the fastest workbooks in the world :D

I am pulling data out from Access from queries and i am clearing range, adding tables with specific names.
And formulas using indirect are working like a charm.

How can i replace indirect formula?
I have a lot of source tables one by one, (one below another).

I can not refer to table directly like "Table1" because when i will delete, and recreate dynamic table i will have error in formula (REF error).

Please help how to change it properly?
To mirror error try to delete source table, and add the same table with the same structure and name.
Indirect will work, the direct formula not.

Best,
Jacek

If you changed the formula in C1 of Sheet1 to this:

=VLOOKUP(A1,Sheet2!A:B,2,0)

then when you delete the table and re-enter it the formula will still work.

I suspect that your example is very simplified.

Hope this helps.

Pete

hi Pete_UK,

it would be good but i can change source tables size and i am adding them one by one in the column.

And if i change table name in a query or add new column i can not refer to it using your formula - so i will have to change all formulas.

With indirect i do not have problem like that because i am refering to table header name - no matter where table is or it was moved or added new columns.

Best,
Jacek

My suggestion would be using VBA to clear the data from the table, connect to the database and import 'new' data and carry on as you were without the need for INDIRECT at all. But impossible to say if that would work for you without sight of the project in a form that's a little more comprehensive than the sample provided.

EDIT: Having now read your subsequent post even the formulas could be updated via VBA so dynamic placement of columns would be accounted for.

BSB

BadlySpelledBuoy you mean delete only table data not whole table and populate it?

It would be good but if you add new column or move some columns or move table it can be a problem.

Best,
Jacek

Yes that's exactly what I mean. You can delete the body of the table but leave the table, including any formulas and formatting, in place then use SQL in the VBA to bring the refreshed data in.

Originally Posted by jaryszek

It would be good but if you add new column or move some columns or move table it can be a problem.

See my edit to post #4.

You could have VBA to reapply all the formulas and find specific columns based on header position so it wouldn't matter if you added new ones or moved existing ones.

BSB

Hi BadlySpelledBuoy,

thank you,

in place then use SQL in the VBA to bring the refreshed data in.

this is interesting, using adodb sql command?
It will be select statement combined with databodyrange from table?
What about headers? How to skip them?

You could have VBA to reapply all the formulas and find specific columns based on header position

what do you mean by this? Do you have any example?

Jacek

Yes, ADODB SQL indeed. I do exactly that to pull data from Access into Excel all the time in my work. The reason is I found that pulling the results from a query built in Access into Excel through a connection often brought back incorrect data if the query was anything more than very straightforward but querying the database directly through SQL in the VBA produced the correct results.

You can query the database and have all the results in a record set which you can then stamp into a table, including headers, and the table will adjust to account for any additional fields/columns youv'e added. Of course that will throw your formulas out but as mentioned, there's possibility to simply overwrite all the formulas using VBA so everything will be realigned by the time it's finished refreshing.

That approach will very much depend on the complexity of your actual workbook. If there are thousands of different formulas then you may not want to update them all each time, but there is the possibility of changing the formulas in the first instance to calculate from "named ranges" rather than table fields or range references. Then when you import the data you could use VBA to simply update all the named ranges and in theory everything will be fine. I say in theory because without seeing the actual workbook it's difficult to say for sure....

BSB

In attachment please find how my source worksheet (Sheet2) is looking.

Issue with keeping tables (not deleting them) is that they can be bigger as new records will added.
So they will change their addresses.

Best,
Jacek

Perhaps a different layout is required then. Additional sheets with one table per sheet? At least that way they will always start at the same point... But I can see why this would not be desirable.

I still don't see the problem with deleting only the DataBodyRange of the tables leaving the structure intact, refreshing the data and if any new columns have been added using VBA to redefine the named ranges' addresses. That way your formulas wouldn't end up showing #REF! and you don't have to reapply potentially thousands of formulas. Doing this would negate the need to use INDIRECT at all because your table names would remain constant.

OR, if you add a new field to the database/query then also add it to the Excel table before you refresh it so that everything can auto adjust prior to new data.

OR, if you're not the one adding the fields to the database then have the VBA check if the headers match prior to bringing in new data, adjust columns by adding new ones where necessary and then refreshing the data.

There are many ways around this problem, but we have so little information on actual structure that it's impossible to give definitive solutions.

BSB

i have to rethink whole idea.
Thank you.

I do not like the idea to keep tables, this can cause a lot of issues.

Now after few seconds i have all queries downloaded into Workbook.
And even users will add 100 hundred new records - it is not harmful for me.

Best,
Jacek

Originally Posted by jaryszek

I do not like the idea to keep tables, this can cause a lot of issues.

I'm not sure what issues you mean, but perhaps the questions should be, will it cause MORE issues than use of INDIRECT is already causing??

BSB

BSB,

yes your question hit the jackpot ;-)

Jacek

O wow, it is good thinking, thank you!

Now i am establishing connection with Access and retreving all tables.
What code should i use to only refresh previously downloaded tables?

Can you help with that?

Best,
Jacek

Yes,

Surely all existing connected tables have been downloaded previously?

aa ok in my code i am unlinking all tables:

Please Login or Register  to view this content.

So if i will not unlink them i should have possibility to refresh them?

And how this linking connections is working? Only inside table queries or has impact also on workbook performance like power query?

Jacek

When you are opening Excel with PQ framenet is loading and is taking a few more seconds = worse performance.

Ok i have to try your approach.
Thank you!

Best wishes,
Jacek