Wondering how to get { } around a formula

[TheBakerBoy]

I am working on a sheet and trying to remove the blanks from a list of recipes so only the selected recipes show up on a drop down box.

White 1

WW 2
Grain 1
Grain 2

White 2

I have a suggested formula for doing that =IFERROR(INDEX(I$2:I$81,SMALL(IF(I$2:I$81<>"",ROW(I$2:I$81)-ROW(I$2)+1),ROWS(H$2:H3))),"") that works well on the sheet that I am copying it from. But the thing is that is has { } on either side of it. Once I click on the forumla bar these disapear, and it doesn't work correctly anymore. The two questions that I have is what are these? and how can I use them?

[protonLeah]

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[AlKey]

Alternatively you can use this formula that doesn't require Ctrl+Shift+Enter.

Please Login or Register  to view this content.

[Tony Valko]

=IFERROR(INDEX(I$2:I$81,SMALL(IF(I$2:I$81<>"",ROW(I$2:I$81)-ROW(I$2)+1),ROWS(H$2:H3))),"")

That formula is an array formula.

http://www.cpearson.com/Excel/ArrayFormulas.aspx

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you must use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you must use the key combo to produce them. Also, anytime you edit an array formula it must be re-entered as an array using the key combo.

This version is a few keystrokes shorter:

=IFERROR(INDEX(I:I,SMALL(IF(I$2:I$81<>"",ROW(I$2:I$81)),ROWS(H$2:H2))),"")

Still array entered!

[TheBakerBoy]

Alternatively you can use this formula that doesn't require Ctrl+Shift+Enter.

Please Login or Register  to view this content.

This did work very well, but the sheet that I am building needs to also work in Excel 2003 and I know that the Iferror function doesn't work. I know that you can use the Iserror function but I am unsure of where to drop this in to the above forumla.

[AlKey]

Are you retrieving numbers or text?

[TheBakerBoy]

It will be text

[AlKey]

Try this one

Please Login or Register  to view this content.

[TheBakerBoy]

I placed the above forumla in the cell and it went blank, when the formula that you gave before worked and gave the the correct answer.

[AlKey]

I placed the above forumla in the cell and it went blank, when the formula that you gave before worked and gave the the correct answer.

It works fine for me. Did you make any adjustments?

[TheBakerBoy]

The only changes I made was to the cells that is referanced, as it fits on the sheet that i am working on. Here is what I changed.
=LOOKUP("zzzz",CHOOSE({1,2},"",INDEX(I:I,SMALL(INDEX((B$2:B$119="")*10^10+ROW(B$2:B$119),0),ROWS(A$2:A2)))))

As said, I placed the above formula in cell A2 and a blank cell showed up. Colum B is where the list of doughs are tabulated and were the blanks do appear. I am unsure how to post a copy of the sheet for you to look at, as that might help.

[AlKey]

To upload a spreadsheet follow instructions below.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[TheBakerBoy]

While I was desensitizing the data (on a new workbook) I plugged in your formula and it worked fine. I went back and place the same formula on the sheet that I am working on, it just turns up a blank. Any ideas?