Expanding a complex range reference

**bob lad** · 03-05-2010, 09:38 AM

Here is a brain teaser for those that love complex formulas. Its been driving me mad for weeks and is preventing me from finishing off my new spreadsheet.

Essentially this forms part of a complex spreadsheet we use in our business for completing quotes for customers. Based on the software items selected for the quote I have used a formula I found on a forum like this to remove the blanks from the list of chosen software items, so that in the final quotation to the customer it appears as a nice neat list.

Although the formula works ok, when I have subsequently added more and more software items to the list of products, the range the formula is looking at to remove the blanks needs extending. I cant for the life of me figure out how to extend it - the obvious route of simply changing the cell references doesnt seem to work.

I have attached my example with subsitute product names that show the list of software items on the left, and the list with the blanks removed on the right. You can see the formuala concerned in cell E3 downwards. You will notice that on the left is has stopped picking up items from the list from the item "Tea" downwards. I need to be able to extend the range to pick up all products. Its driving me mad.

Thank you for your help in advance.

Kind regards,

Rob

**JBeaucaire** · 03-05-2010, 10:39 AM

Hi Bob. Welcome to the forum, this place is an awesome resource.

Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to edit that post above and change your title to specifically state the topic, as per forum rule #1.

Something like: "Automatically expanding a range reference"

**bob lad** · 03-05-2010, 10:46 AM

Apologies. Done. Just reading the rules now.

**JBeaucaire** · 03-05-2010, 10:49 AM

Here...I appreciate the SMALL(IF() construct, but I'm not a huge fan. And if your potential data list could get very long, performance issues with your sheet could set in very quickly.

I prefer to use key columns to add unique index numbers. It's simple to create a list from an index.
Have a look.

**bob lad** · 03-05-2010, 10:53 AM

Well my original post title of "calling all excel experts" was appropriate after all. You are a guru. Im not quite sure what you have done but it certainly achieves the effect and I can have a good play with it and see how it works.

Many thanks and much appreciated. Have a good weekend.

One happy customer.

Bob Lad.

**JBeaucaire** · 03-05-2010, 11:01 AM

My pleasure. Glad to help.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)