1. click A

2. F5 - Special

3. Constants, OK

4. Ctrl + C

5. Click B1

6. Ctrl + V

:) we can save this a s macro I guess

Solved

Posted on 2011-04-18

I would like a formula to list cell contents without blank rows

please see attached spreadsheet

Many thanks

Ian

Excel-sort-Formula.xlsx

please see attached spreadsheet

Many thanks

Ian

Excel-sort-Formula.xlsx

11 Comments

1. click A

2. F5 - Special

3. Constants, OK

4. Ctrl + C

5. Click B1

6. Ctrl + V

:) we can save this a s macro I guess

=INDEX(A$1:A$100,SMALL(IF(

To array-enter a formula, hold the Control and Shift keys down, then hit Enter. Excel should respond by adding curly braces { } surrounding your formula. If it does not, then select the cell, click in the formula bar and CTRL + Shift + Enter once again.

If your formula returns the same answer as you copy it down, make sure the workbook is in Automatic calculation mode.

Brad

=IFERROR(INDEX(A$1:A$100,S

Still an array formula, so remember to CTRL + Shift + Enter

Brad

Sorry for lengthy delay getting back to you. Been extremely busy.

Thank you kindly for the formula it works well

I would like to have same formula copied to adjoining column for "Sell"

Attached is the worksheet if you wouldn't mind taking a look

Many thanks Buy-Sell-2.xlsx

I changed your column H formula to:

=IFERROR(INDEX(D$3:D$100,S

Your column I formula then becomes:

=IFERROR(INDEX(F$3:F$100,S

Brad

Buy-SellQ26963699.xlsx

Thank you kindly

Just one question.

Why is it the formula does not work past 100 rows

after I change formula from 1:100 to say 1:150.? if doesn't seem to work

it displays blank cells

I need around max of 2000 rows

Cheers

Ian

Did you remember to Control + Shift + Enter?

I used this:

=IFERROR(INDEX(D$3:D$2000,

See attached workbook.

Brad

Buy-SellQ26963699.xlsx

Thanks Brad it all works well

Although completely unnecessary for your problem, it is possible to create an array formula and apply it to more than one cell at the same time. If you do, then you get the error message described when you try to change a formula within that range.

What you need to do is to clear the formula from the cells where it was array-entered as a block. Then put the formula in a single cell, Control + Shift + Enter, and copy it down as required.

Brad

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Shift Rows Down, after copying from one sheet to another | 7 | 34 | |

Excel line breaks | 1 | 19 | |

Excel remove Chars in Conditional formating. | 6 | 19 | |

How to Autofill Across to next value | 3 | 20 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**13** Experts available now in Live!