Index Small function to skip blank rows

  • Re: Index Small function to skip blank rows


    It's hard to see your image. If I enlarge it everything is blurred.... perhaps an actual Excel attachment would be better?


    From what I can tell from the image, I don't see why you need to "skip blanks". If your condition is checking if a range has a match to the value in Q2, then the blanks in that range won't be considered.


    Are you confirming the formula as an Array* formula?
    [arf]*[/arf]


    or am I missing something else?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Index Small function to skip blank rows


    Attached is the workbook. If I select columns A6:L6 and drag down one row at a time it copies the formulas as I need it; as shown in rows 12:21. But, I need it to do it with the blank rows as shown for panels A and B in rows 6:11. If I drag down A6:L8 then row 9 will be the data for panel D and not panel B so it is skipping panels B & C.

    Files

    • DP Test.xlsx

      (1.59 MB, downloaded 115 times, last: )
  • Re: Index Small function to skip blank rows


    First question would be, why do you need blank rows between panels A and B and B and C?


    Second question would be, how would we know when you would want to skip rows? Is there any indicator that there should be blanks between certain panels? Formulas being copied down need a certain logic to decide on when to show data and when not to.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Index Small function to skip blank rows


    The destination worksheet uses three merged rows per each entry. Since arrays do not work with merged cell I have to enter them individually then merge the arrayed boxes after its finished.


    Sheet 1 is how it starts and Sheet 1 (2) is how it needs to look when finished.

    Files

    • DP Test.xlsx

      (1.6 MB, downloaded 87 times, last: )
  • Re: Index Small function to skip blank rows


    Although it is never recommended to use formulas in Merged cell setup because, as you can see, it is nothing but headacheds.... perhaps try changing the k factor in the SMALL() function to:


    [COLOR="#0000FF"]COUNTA($A$3:$A3)[/COLOR]


    So, unmerge A6:A8 temporarily and apply Array* formula:
    [COLOR="#0000FF"]=IFERROR(INDEX(Fabric!$B$26:$B$988,SMALL(IF($Q$2=Fabric!$A$26:$A$988,ROW(Fabric!$A$26:$A$988)-MIN(ROW(Fabric!$A$26:$A$988))+1,""),COUNTA($A$3:$A3))),"")[/COLOR]
    [arf]*[/arf]


    Then merge back A6:A8 and then copy formula down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Index Small function to skip blank rows


    So, this did solve the issue but a new issue has arose. If you refer to the excel file in this thread "DP Test" and click on the sheet 1 tab please look at column L. How do I now insert a blank row between the different colors? Rows 6-20 can all be consecutive as shown but I need a blank row between 20 and 21, panel Q and Z since they are different colors. Thanks in advance for any help.

  • Re: Index Small function to skip blank rows


    Hi, just a thought, why don't you write a short macro to insert the empty rows where you need them? I am guessing you have some macros in your original worksheets to work when the buttons are used.


    As I say just an idea.....

    R



    BTW: If the reply someone gave helped you, it is nice to thank them :wink:. You can also recognize their contribution by using the reputation button!