Formula Help - Prevent Row from Changing when new row inserted

nhbartos

Board Regular
Joined
May 23, 2015
Messages
148
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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
Hi,

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

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

nhbartos

Board Regular
Joined
May 23, 2015
Messages
148
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)
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
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.
 

nhbartos

Board Regular
Joined
May 23, 2015
Messages
148

ADVERTISEMENT

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)))
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
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")
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,983
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,589
Messages
5,637,278
Members
416,963
Latest member
samfuge

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?

Disable AdBlock

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
Top