Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Formula - Truly Absolute Reference

  1. #1
    Board Regular
    Join Date
    Dec 2017
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    45,526
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Formula - Truly Absolute Reference

    Please post your formula, and let us know what cell you are putting it in.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Dec 2017
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula - Truly Absolute Reference

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

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,511
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Formula - Truly Absolute Reference

    Quote Originally Posted by SteveOranjin View Post
    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"))
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular
    Join Date
    Dec 2017
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula - Truly Absolute Reference

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

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,511
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Formula - Truly Absolute Reference

    Quote Originally Posted by SteveOranjin View Post
    =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"))
    Last edited by Rick Rothstein; Feb 19th, 2018 at 04:16 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular
    Join Date
    Dec 2017
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  8. #8
    Board Regular
    Join Date
    Dec 2017
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula - Truly Absolute Reference

    Hot Dog!

    It worked.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •