We help IT Professionals succeed at work.

Autorefresh tablelist from another table based on criteria

Hey Experts,

In a excel workbook I have two sheets called DB and List. Each of the sheets has a table in it. The DB table holds names, and each name can be either active or inactive (Initials are short for the name).

When I activate the List sheet, I want the initials column in the List table to populate only with names, that are active in the DB table. Notice that there are other columns in the list, that hold calculations, that depends on content in the Initials column.

When I leave the sheet List, by performance reasons I want the table to empty itself, but still keep the first row in it, so that the calculations remain. Let me know, if there are other ways to keep performance at a max.

Excel version 2007 on Windows XP

Regards, Raahaugen
solution.xlsx
Comment
Watch Question

you could play with automatic vs manual calculations.
using manual calculations, formula's will only be calculated when you manually tell them to.
This can greatly increase the speed of regular work on data values (CRUD operations).
If you are looking for a decrease in file size, emptying the tables is the way to go.

Author

Commented:
Thanks for the hint, akoster!

The primary q is still to be solved :-)

/Raahaugen
that would be


solution.xlsm

Author

Commented:
Great job!

I would like, if the first row is actually also the first of the initials. Possible?
- Then the deal is to just not erase it when worksheet deactivates, so that the calculations stays :-)

But great job! :-)

/Raahaugen
you could update the worksheet_deactivate code to:

Private Sub Worksheet_Deactivate()
Dim pos As Integer

    For pos = ListObjects(1).ListRows.Count To 1 Step -1
        ListObjects(1).ListRows(pos).Delete
    Next pos

End Sub

Open in new window

Author

Commented:
Working perfectly. Thanks!

/Raahaugen

Author

Commented:
I've posted a new question in which I ask for a more efficient way of doing what you have suggested. The thing is, it takes 10 sec to activate and deactivate - and that is before calculation.

Please feel free to "update" your code in the new question:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27488405.html

Regards, Raahaugen