Last populated row

[jamescooper]

Can anyone help please?

=COUNTIF(Calculations!$L$3:$L$100000,WACM!$G9,Calculations!$I$3:$I$100000)

If I have this range and instead of putting 100000 as the last row, how would I put the last row populated? If I had last row populated in another cell, could I link to this formula?

Many thanks.

 

[jamescooper]

Any ideas?

 

[thisoldman]

One formula for a dynamic column would be:
=$L$3:INDEX($L:$L, COUNTA($L:$L) + 2)

Other people use an OFFSET formula:
=$L$3:OFFSET($L$3, COUNTA($L:$L) - 1, 0)

The data in column L can't include blanks. I'm assuming no headers and no populated cells in the column outside of the data range. If there are other populated cells, you have to adjust the "+ 2" or the "- 1" in the two formulas.

I use these incantations as named formulas. A COUNTIF formula might become
=COUNTIF(MyColumnEllData,WACM!$G9)

I haven't found a way to use the defined name dynamic ranges in formulas with INDIRECT.

Another way of creating dynamic ranges is to convert the data to a structured table. The columns (fields) automatically become dynamic and are given named references. A COUNTIF formula might become:
=COUNTIF(Table1[Field12],WACM!$G9).

There are many good tutorials available. Google "excel dynamic ranges" and "excel tables"