Gaps in a List

[anar_baku]

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 rang
B1:B50. The problem is that data in this range is dynamic, i.e. it ha
formulas and depending on the result of these formulae the cells in th
range either have a value or the cell is left blank. The problem thi
causes is that the list ends up having gaps in it because it uses blan
cells as well. And this is despite me specifcally ticking " Ignore Blan
" in the Data Validation menu where I'm creating the list.

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

Many thanks,

Ana

 

[Guest]

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]

No, I don't want the cell to show anything if it shouldn't, but then th
cells that are empty to show up in my list as gaps. The reason havin
gaps in a list is that lists shouldn't have gaps in them! This i
especially problematic if you have a big list and you have big gaps i
the list.

I think you are confusing as to which list I'm referring to. I'
referring to dropdown lists

 

[Ron Coderre]

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