Problem with COUNTA "NOT" counting empty cells

  • Hi,

    I am using two dynamic lists, one is dependent on the other. The dependent list has some empty cells to seperate different catagories. The dependent list looks like this:

    Fruits
    Apple - Blue
    Apple - Red
    Apple - Green

    Melon - White

    Banana -Yellow
    Banana - Green

    Since this list is a dynamic dependent one, I had to use OFFSET, INDIRECT and COUNTA formulas. The COUNTA messes up my list because it counts non empty cells. The drop down menu it looks like:

    Fruit
    Apple - Blue
    Apple - Red
    Apple - Green

    Melon - White

    Because the COUNTA ignores the two empty cells seperating the categories, it effectively cuts the two last entries from the drop down list. I could remove the empty cells and skip the seperation of categories, but I prefer not to since I want the xls file to be as user friendly as possible. This means that I also have to use dynamic name ranges for easy update/adding new entries. I was hoping someone could give me some tips to how to solve this issue. One solution I was thinking of was to count cells untill two emtpy cells in a row is found. But I don't know if this can be done in function form.

    The formula used in the dynamic dependent list is:
    =OFFSET(INDIRECT(SUBSTITUTE($A$2;" ";"_"));0;0;COUNTA(INDIRECT(SUBSTITUTE($A$2;" ";"_")&"Col"));1)

  • Re: Problem with COUNTA "NOT" counting empty cells


    You can use the countblank function:


    =OFFSET(INDIRECT(SUBSTITUTE($A$2;" ";"_"));0;0;COUNTBLANK(INDIRECT(SUBSTITUTE($A$2;" ";"_")&"Col"))+COUNTA(INDIRECT(SUBSTITUTE($A$2;" ";"_")&"Col"));1)

    Regards,


    WidgetWonka
    Puuuureeee Imagination

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!