Formula Help - Prevent Row from Changing when new row inserted

[nhbartos]

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?

 

[RickXL]

Hi,

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

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

 

[nhbartos]

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]

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]

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]

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]

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)

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