Gaps in a List

Hi all,

I'm having a problem with a list that I've created.

The list is in cell A1, the base data for the list is in the range B1:B50. The problem is that data in this range is dynamic, i.e. it has formulas and depending on the result of these formulae the cells in the range either have a value or the cell is left blank. The problem this causes is that the list ends up having gaps in it because it uses blank cells as well. And this is despite me specifcally ticking " Ignore Blank " in the Data Validation menu where I'm creating the list.

Any help on this annoying problem would be very much appreciated.

Many thanks,

Anar

So do you want every cell to show up with some sort of character or word that
shows you that a formula is there? And why specifically is having gaps a
problem?

"anar_baku" wrote:

>
> Hi all,
>
> I'm having a problem with a list that I've created.
>
> The list is in cell A1, the base data for the list is in the range
> B1:B50. The problem is that data in this range is dynamic, i.e. it has
> formulas and depending on the result of these formulae the cells in the
> range either have a value or the cell is left blank. The problem this
> causes is that the list ends up having gaps in it because it uses blank
> cells as well. And this is despite me specifcally ticking " Ignore Blank
> " in the Data Validation menu where I'm creating the list.
>
> Any help on this annoying problem would be very much appreciated.
>
> Many thanks,
>
> Anar
>
>
> --
> anar_baku
> ------------------------------------------------------------------------
> anar_baku's Profile: http://www.excelforum.com/member.php...o&userid=18259
> View this thread: http://www.excelforum.com/showthread...hreadid=541591
>
>

Anar:

Here's something you might be able to use:

Since your intended DV list is populated by formulas and some of those return "", you'll need to use a separate range that is based on your original list, but lists any "" values at the end of the list.

So....here's my suggestion

1)Make no changes to the B1:B50 range formulas

2)C1: MyDynList (or whatever name you'd like)

3)Enter this ARRAY FORMULA (which you commit by holding down [Ctrl] and [Shift] when you press [Enter] in cell C2.
(I broke the formula in 4 pieces to compensate for screen wrap. Combine all 4 pieces in C2):
=IF(SUMPRODUCT(($B$1:$B$51<>"")*ISERROR(MATCH
($B$1:$B$51,$C$1:C1,0)))<>0,INDEX($B$1:$B$51
,MATCH(TRUE,ISERROR(IF(($B$1:$B$51=""),FALSE
,MATCH($B$1:$B$51,$C$1:$C1,0))),0),1),"")

(Note: there are NO spaces in the resulting formula)

4)Copy C2 and paste it into C3:C51

That should create a list of B1:B50 items that are not "blank".

5)Create a Dynamic Range Name based on C2:C51
<insert><name><define>
Names in Workbook: MyDynList (or whatever name you choose)
Refers to: =OFFSET(Sheet1!$C$2,,,COUNTIF(Sheet1!$C$2:$C$51,"?*"),1)
Click the [OK] button

(Of course, if your sheet is not named Sheet1, use your actual sheet name)
Now...all the pieces are in place.

6)Set up the Data Validation:
Select cell A1 (your DV input cell)
<data><validation>
Allow: List
Source: =MyDynList (or whatever you named the dynamic named range)
Click the [OK] button

Done.

Does that get you where you want to be?

Regards,
Ron