# How to formulate no-blanks array formula?

#### haruspication

##### New Member
In my worksheet I have a table (RECIPES) with a list of ingredients in column A (A2:A50), a list of recipes in row 1(B1:K1), and the quantity of each ingredient required for each recipe in the range (B2:K50). On a different worksheet I created a new table (DAILYMENU) and in row A1 there is a header for each type of daily meal (breakfast(A2), snack(A4), lunch(A6), snack(A8), dinner(A10)). Under the headers is another row with a drop down menu to select the recipe name, using a data validation list based on the recipes from the RECIPES table.

My question is: How can I list only non-blank quantities and ingredient names from my RECIPES table in my daily menu worksheet based on the recipe selected for each meal? I'm trying to figure out how to use the non-blank array formula so that I can eliminate "blank" quantities from appearing in the list. But you usually have to indicate a defined column from which to pull the data. In this case the column isn't "defined" per se, but rather dependent on the recipe choice from row B in the DAILYMENU table. What formula can I use to list non-blank ingredients and their quantities in DAILYMENU table based on the recipe chosen from RECIPES table? Some kind of "if then" formula along with the array formula?

The basic no-blank array formula I started with is from cpearson's website:

(IF(blanksrange<>"",ROW(blanksrange),ROW()+ROWS(blanksrange))),
ROW()-ROW(noblanksrange)+1),COLUMN(blanksrange),4)))

Any help is appreciated.

#### pgc01

##### MrExcel MVP
Hi
Welcome to the board

This one works for me. In Sheet2!A2:

=IF(ROWS(\$A\$2:A2)>COUNT(INDEX(Sheet1!\$B\$2:\$F\$6,0,MATCH(A\$1,Sheet1!\$B\$1:\$F\$1,0))),"",INDEX(Sheet1!\$A\$2:\$A\$6,SMALL(IF(INDEX(Sheet1!\$B\$2:\$F\$6,0,MATCH(A\$1,Sheet1!\$B\$1:\$F\$1,0))<>"",ROW(Sheet1!\$A\$2:\$A\$6)-ROW(Sheet1!\$A\$2)+1),ROWS(\$A\$2:A2))))

This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER

Copy down and accross

Last edited:

#### haruspication

##### New Member
Thank you! I simulated your exercise using your formula - perfect! I can't wait to apply it to my spreadsheet (this will make planning groceries, nutrition, etc., so much easier each week!).

Thanks, again,
Megan

#### pgc01

##### MrExcel MVP
Megan, I'm glad it helped. Thanks for the feedback.

1,082,335
Messages
5,364,696
Members
400,811
Latest member
MSBINinja

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...