Thanks:  0
Likes:  0

# Thread: Formula - Truly Absolute Reference

1. ## Formula - Truly Absolute Reference

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

2. ## Re: Formula - Truly Absolute Reference

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

3. ## Re: Formula - Truly Absolute Reference

Originally Posted by Joe4
Please post your formula, and let us know what cell you are putting it in.
=COUNTA(Sheet1!A\$:A\$)-1

4. ## Re: Formula - Truly Absolute Reference

Originally Posted by SteveOranjin
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"))

5. ## Re: Formula - Truly Absolute Reference

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

6. ## Re: Formula - Truly Absolute Reference

Originally Posted by SteveOranjin
=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"))

7. ## Re: Formula - Truly Absolute Reference

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.

Hot Dog!

It worked.