# Formula Help - Prevent Row from Changing when new row inserted

#### nhbartos

##### Board Regular
Hello...

I have a CountA formula, see below...

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

I have a macro that creates a new client data row in row 10, and all other client data shifts down by 1. It works fine.

Now, I have some formulas on Report tab that pulls this data using similar formulas like the above.

But, when a new row is inserted in row 10 for a new client add, the formula changes to the below:

=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.

Thoughts?

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi,

I tried this not really expecting it to work but it seems to:

=COUNTA(INDIRECT("B10"):B108)

Hi, thanks...

How would I write the formula if it is pointing to another tab?
I tried to figure it out, but couldn't make it work.

This is what it looks like linked to another tab.

=COUNTA('0 Data Summary'!\$B\$10:B108)

Hi,

I am pleased you asked that question

At least I ended up with something more like what I was expecting in the first place.

Please try this and accept my apologies for doing only half the job.

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

It works like this:

One string is fixed: "'0 Data Summary'!B10:B"
A second is based on the row number of row 108 in the summary sheet: ROW('0 Data Summary'!B108)
That will resolve to 108.

Putting the strings together we get: "'0 Data Summary'!B10:B108"
INDIRECT then takes that and makes it a real address and not just a string.

When rows are added in the summary sheet then B10 will always remain fixed because it is in a fixed string.
The end row will vary because the value inside the ROW() function will change.

Hi RickXL....that worked perfect! Thanks!

What if I want to add another criteria and make it a countif or countifs???

I have been trying to add in this to your formula at the bottom: B10:B108,">1/1/2014"

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

Hi,

The part inside the INDIRECT() is just a range like "B10:B108" so for a COUNTIF() you need to add the comma after that:

=COUNTIF(INDIRECT("'0 Data Summary'!B10:B"&ROW('0 Data Summary'!B108)),">1/1/2014")

Rather than having all those INDIRECT functions, use this code to insert the new row, the formulas like =COUNTA('0 Data Summary'!B10:B108) will adjust to =COUNTA('0 Data Summary'!B10:B109)

Code:
``````With Range("10:10")
.Offset(1, 0).Insert
.Copy Destination:=.Offset(1, 0)
.ClearContents
End With``````

Replies
7
Views
389
Replies
5
Views
230
Replies
7
Views
437
Replies
1
Views
418
Replies
6
Views
214

1,219,770
Messages
6,150,165
Members
450,937
Latest member
kattyg261

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back