Let´s create one excel file containing one sheet named "Sheet1" with a list named "Table1". (Sorry I am not sure about wording, i.e. Lists vs. Tables, in non-German Excel releases.) Populate it with some values so it looks like so:
Name
ValA
ValB
ValC
ValD
"Name" ist the column header. Note this should be a list table, not just a cellrange.
Now lets verify we can get the count of non-empty cells in the list. Enter this Formula into any blank cell in this workbook:
=COUNTA(Table1[Name])
This will always evaluate to 4 if you don't mess around with the asthetic state of my Table1 table.
Save this workbook as X.xlsx. Don't close it yet.
Now in an additional Excel window, create a new workbook, and enter the same formula. Of course, to calculate the other workbook sheet´s table row count, we have to qualify the table by the workbook file, like so:
=COUNTA('PathToMyExcelFiles\X.xlsx'!Table1[Name])
where "PathToMyExcelFiles" is the local path of the xlsx. References like these are easily created using excel by pointing to the cell range when entering the formula.
This beautifully evaluates to 4. Perfect.
Now close the X.xlsx window so the X.xlsx is not open anymore :), and force a recalc (or alternatively, close all xlsx windows, and create a new one with the formula shown without opening X.xlsx first). The qualified version of the formula now evaluates to 1.
Why?
If the workbook we refer to is not loaded, COUNTA will return 1, otherwise the correct 4.
Background: I am looking for ways of storing lists in tables in a centralized spreadsheet, and use those lists from other spreadsheets in validity lists.
I am using Excel 2010, and yes I absolutely want to use Excel table lists to store the lists.
