Formula - Truly Absolute Reference

[SteveOranjin]

Hello,

I've got a simple count formula that I use as a part of a worksheet I use for work. One of the formulas I have in it is designed to count the number of rows populated in column A. But if I delete a column, and I often times need to, the cell reference goes to null. My question is, is there a way to reference the first row of a sheet - so it is effectively referencing that first row - A - absolutely, even if I should delete A and a new "A" column should take the place of the old one?

Steve

 

[Joe4]

Please post your formula, and let us know what cell you are putting it in.

 

[SteveOranjin]

Please post your formula, and let us know what cell you are putting it in.

=COUNTA(Sheet1!A$:A$)-1

 

[Rick Rothstein]

I've got a simple count formula that I use as a part of a worksheet I use for work. One of the formulas I have in it is designed to count the number of rows populated in column A. But if I delete a column, and I often times need to, the cell reference goes to null. My question is, is there a way to reference the first row of a sheet - so it is effectively referencing that first row - A - absolutely, even if I should delete A and a new "A" column should take the place of the old one?

It sounds like you may be looking for something like this...

=COUNTA(INDIRECT("A:A"))

 

[SteveOranjin]

=COUNTA(INDIRECT(Sheet1!A:A))
It gives me a grand total of 1

 

[Rick Rothstein]

=COUNTA(INDIRECT(Sheet1!A:A))
It gives me a grand total of 1

Not if you include the quote marks that my formula was posted with (the INDIRECT function requires a text argument)...

=COUNTA(INDIRECT("Sheet1!A:A"))

 

[SteveOranjin]

I didn't make myself very clear there. For example - it will in MOST Cased be filled with the name of a company. So it will have data that says something to the effect of, "Comcast". or "Matco Norca". The Cell A1 will have a header.

I believe that the formula is good. The problem is, that at times I delete columns. In which case, the column that the formula was referencing is gone. There is also a header, which i usually subtract from the count.

 

[SteveOranjin]

Hot Dog!

It worked.