0

I have a CountA formula:

=COUNTA('0 Data Summary'!B10:B108)

and a macro that creates a new client data row in row 10 every time and all other client data shifts down by one row. It works fine but, when a new row is inserted in row 10 via macro (for a new client add) the formula changes to:

=COUNTA('0 Data Summary'!B11:B109)

I want it to keep the B10 and not change to B11. The change to 109 is fine because I want it to remain within the range.

I tried putting in the dollar signs but it didn't work.

4
  • The only way I know to accomplish this is with the INDIRECT function like =COUNTA(INDIRECT("'0 Data Summary'!B10:B108")).
    – user385793
    May 30, 2015 at 22:49
  • Possible duplciate of Stuck in excel formula calling data from another sheet without changing cell reference.
    – user385793
    May 30, 2015 at 23:09
  • 1
    Oh well. That would be (slightly) different then. :) =COUNTA(INDIRECT("'0 Data Summary'!B10:B"&ROW(108:108)))
    – user385793
    May 30, 2015 at 23:18
  • You could use Index instead of volatile Indirect: =COUNTA(INDEX(B:B,10):B108)
    – teylyn
    May 31, 2015 at 1:11

2 Answers 2

2

You can make the first reference always refer to the 10th row in column B by using Index, like

=COUNTA(INDEX('0 Data Summary'!B:B,10):'0 Data Summary'!B109)
1
0

Similar to teylyn's answer, you could anchor the starting address in concrete by making its address a text string:

=COUNTA(INDIRECT("'0 Data Summary'!B10"):'0 Data Summary'!B108)

and using the INDIRECT() function to de-reference that string (i.e., to treat it as a cell address and retrieve the contents of the identified cell).

1
  • Keep in mind that Indirect is volatile and will cause a complete recalc of the worksheet when any cell is changed. Volatile functions can cause slowness with large workbooks.
    – teylyn
    May 31, 2015 at 7:28

You must log in to answer this question.