Excel 2010 - External Data Table Fill Down Modifying RANGE on Refresh

Question

My goal is to incrementally count how many times a value is used in a range.

My data:

     A              B

1    Yellow  1

2    Blue            1

3    Green          1

4    Blue            2

5    Blue            3    

6    Green          2

7    Yellow         2

8    Green          3

9    Green          4

10  Green          5

The formula in B1 =COUNTIF($A$1:A1,A1), B2 is =COUNTIF($A$1:A2,A2) and C2 =COUNTIF($A$1:A3,A3), etc.

Column A comes from an External Data source (ODBC connection to a DBF (DB4) database) and is relfected in Excel 2010 as a Table.

Column B is an adjacent column and as such the formula will "fill down" when the external data grows beyond Row 10, i.e. 15.

The problem is that when the fill down occurs to Row 15 the RANGE in the COUNTIF formula is modifying itself to include the ENTIRE SCOPE of $A$1:A15 on ALL ROWS instead stopping at the Row it is on.

All rows get =COUNTIF($A$1:A15,A#) where # = my Row Number instead of the formulas in my original example of B1 =COUNTIF($A$1:A1,A1), B2 is =COUNTIF($A$1:A2,A2) and C2 =COUNTIF($A$1:A3,A3), etc.

I dont know if this is a bug in Excel or why this is happening. 

My settings in my External Data Properties are:

Include row numbers = FALSE

Preserve column sort/filter/layout = TRUE

Adjust Colum width = TRUE

If the number of rows in the data range changes upon refresh = Overwrite existing cells with new data, clear unsed cells

I have tried all 3 options in the last setting and get the same resulting bad RANGE each time.

Help me Obi Wan Kenobi, you're my only hope!

Answer 1

I understand that.  And I do want that feature to apply, I do want the automatic Fill Down to occur.  The problem is that I think the Fill Down is populating the formulas incorrectly. 

Before a refresh my formulas are as I entered them:

B1: =COUNTIF($A$1:A1,A1)

B2: =COUNTIF($A$1:A2,A2)

B3: =COUNTIF($A$1:A3,A3)

After the Refresh (as the table grows or shrinks) the formulas are:

B1: =COUNTIF($A$1:A3,A1)

B2: =COUNTIF($A$1:A3,A2)

B3: =COUNTIF($A$1:A3,A3)

It is changing the RANGE portion of the COUNTIF to include the entire SCOPE of the RANGE instead of just the rows above as in my Before sample.

Answer 2

Hi,

The data that comes into MS Excel from the External Data Source is in an Excel Table format.  One of the features of an Excel table is that formulas in adjacent columns automatically get filled up when the formula is written in a single cell.  This is why you are facing the problem.

If you convert the Table into a Range, then the external link will break though the formula problem will get resolved.

Answer 3

If I do that I wont get the automatic Fill Down scaling as the data returned by the External Data Query grows and shrinks.  As I understand it this feature only works when Formula Cells are Adjacent to Table Cells.

Answer 4

Hi,

Do you face the same problem if you write the formula in column C instead of column B I..e. keep column B blank and write the formula in column C.

Does it work?