+ Reply to Thread
Results 1 to 13 of 13

Countblank

  1. #1
    Registered User
    Join Date
    02-10-2009
    Location
    Conneticut, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Countblank

    I have this formula that i will be doing a paste special (formula) on

    Please Login or Register  to view this content.
    it should number my rows 1,2,3,4,5,ect.. with my different sized merged cells (hence why i just dont drag the numbers to automatically number my rows, it throws a error "This operation requires the merged cells to be identically sized numbers")

    why will it not let me combine the cell call with the COUNTBLANK?

    i also know VB and if anyone knows a macro that can help me or a different formula that would be great but this should work.
    ?We learn more by looking for the answer to a question and not finding it than we do from learning the answer itself.?

  2. #2
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208

    Re: Countblank question

    Try using INDIRECT:

    =(CELL("row")-CELL("row",B109)-COUNTBLANK(INDIRECT("B129:"&CELL("address"))))

    HTH,
    Jason

  3. #3
    Registered User
    Join Date
    02-10-2009
    Location
    Conneticut, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Countblank question

    Humm returns Zero maybe a countif

    Please Login or Register  to view this content.
    i tried that indirect it did not work just returned zero again

  4. #4
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208

    Re: Countblank question

    Is it possible for you to post up an example workbook and exactly what you are looking to accomplish? I am not clear exactly want you need at this point.

  5. #5
    Registered User
    Join Date
    02-10-2009
    Location
    Conneticut, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Countblank question

    I'm attempting to number Steps on a testing procedure by numbering the rows but some test steps and multiple combined rows that are combined using merge cells and i cannot start the sequence of numbers by filling in the first 2 rows and dragging the auto complete down to automatically fill in the step numbers (the rows) from 1 to 2264.

    Because some steps contain multiple merged rows i cannot use the auto complete function because it throws this error "This operation requires the merged cells to be identically sized numbers".
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208

    Re: Countblank question

    Ok, this is a good start. How is the rest of the data laid out? How do cells B109 and B129 come into play?

  7. #7
    Registered User
    Join Date
    02-10-2009
    Location
    Conneticut, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Countblank question

    B109 is where the data starts and b129 is the step i got to manually typing the number in and realized it would take forever to do 2000+ steps. so i need a call formula or a VB script that numbers these steps

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Countblank question

    Based on nothing more than your sample file the below would be a very rough example...

    Please Login or Register  to view this content.
    obviously you will need to revise your range (start/end row based on range content in another column perhaps ?)

    One point I would make re: pasting formulae into merged areas etc is that even if the results appears only in top left cell of area the formula still exists in all cells that are not visible... to illustrate using your file... if you were to enter:

    A2: =1+MAX(A$1:A1)

    and copy / paste special / formula over A3:A12 you would find that the "hidden" cells are still populated and return results as they would were they not merged.

  9. #9
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208

    Re: Countblank question

    For me, it is much easier to write a macro for this:

    Please Login or Register  to view this content.
    Does this work for you?

  10. #10
    Registered User
    Join Date
    02-10-2009
    Location
    Conneticut, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Countblank question

    wow your going to laugh but..

    =B109+1

    in cell B110 and then i did a copy and a paste special formula on the selected range of cells and it worked....i could of swore i tried this before i even posted on the forums.
    Last edited by JBurlison; 12-04-2009 at 04:33 PM.

  11. #11
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208

    Re: Countblank question

    Interesting, that worked? I just tried it, and it did not for me (it skips some numbers). See attached screen print.
    Attached Images Attached Images

  12. #12
    Registered User
    Join Date
    02-10-2009
    Location
    Conneticut, USA
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Countblank question

    I'm running excel 2010

  13. #13
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208

    Re: Countblank question

    I guess it works differently than 2003. Good to hear you got it working!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1