Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
The reason COUNTA() returns 1 is that 'PathToMyExcelFiles\X.xlsx'!Table1[Name] is returning a #REF! error when X.xlsx is closed; and COUNTA(#REF!) = 1. This seems to be a limitation of tables. –  Rhys Gibson Jan 4 '12 at 19:15
    
So the referenced xls must be opened before the reference is resolved correctly? This defeats reason for doing the reference at all :( –  TheBlastOne Jan 5 '12 at 11:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.