How create a nonblank source for a verification list with a mix of filled & empty cells?

Hi,
I have a source range containing a mix of filled and empty cells with name
"ListSource"

A1: 111
A2: 222
A3:
A4: 444
A5: 555
A6:
A7: 777
A8:
A9:
A10:

In the drop down verification on another sheet I refere to "=ListSource" and
there it is - all cells including the empty ones. I want the list to show:

111
222
444
555
777

The ListSource has to be in a row, therefore can I not create the list just
with a Autofilter macro...

I have also tried the 'nonVBA way' with John Walkenbach's forumla "Returning
Nonblank Cells from a Range" without been able to reconstruct his result
(oh, yes - i used ctr-shift-enter to make arrays).
{=IF(ISERR(SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))),"",INDEX(Data,SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))))}

For the very interested i translated it to swedish
{=OM(ÄRFEL(MINSTA(OM(YourData<>"";RAD(INDIREKT("1:"&RADER(YourData))));RAD(INDIREKT("1:"&RADER(YourData)))));"";INDEX(YourData;MINSTA(OM(YourData<>"";RAD(INDIREKT("1:"&RADER(YourData))));RAD(INDIREKT("1:"&RADER(YourData))))))}
..

Maybe I didn't get something right in the interpretation, but hey, it's
better to do it in VBA that I know, rather than trying to make it with this
long formula (even better would be to fully understand the formula, I admit
;-)

Anybody back from summer vacations yet?
/ Regards !

Use code to fill the listbox.


ListBox1.rowsource = ""
Listbox1.Clear
for each cell in Range("ListSource")
if cell.Text <> "" then
Listbox1.AddItem cell.value
end if
next

--
Regards,
Tom Ogilvy


"Maria J-son" <NotAEmail@hotmail.com> wrote in message
news:uUm7nMDnFHA.3936@TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a source range containing a mix of filled and empty cells with name
> "ListSource"
>
> A1: 111
> A2: 222
> A3:
> A4: 444
> A5: 555
> A6:
> A7: 777
> A8:
> A9:
> A10:
>
> In the drop down verification on another sheet I refere to "=ListSource"
and
> there it is - all cells including the empty ones. I want the list to show:
>
> 111
> 222
> 444
> 555
> 777
>
> The ListSource has to be in a row, therefore can I not create the list
just
> with a Autofilter macro...
>
> I have also tried the 'nonVBA way' with John Walkenbach's forumla
"Returning
> Nonblank Cells from a Range" without been able to reconstruct his result
> (oh, yes - i used ctr-shift-enter to make arrays).
>
{=IF(ISERR(SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1
:"&ROWS(Data))))),"",INDEX(Data,SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Dat
a)))),ROW(INDIRECT("1:"&ROWS(Data))))))}
>
> For the very interested i translated it to swedish
>
{=OM(ÄRFEL(MINSTA(OM(YourData<>"";RAD(INDIREKT("1:"&RADER(YourData))));RAD(I
NDIREKT("1:"&RADER(YourData)))));"";INDEX(YourData;MINSTA(OM(YourData<>"";RA
D(INDIREKT("1:"&RADER(YourData))));RAD(INDIREKT("1:"&RADER(YourData))))))}
> .
>
> Maybe I didn't get something right in the interpretation, but hey, it's
> better to do it in VBA that I know, rather than trying to make it with
this
> long formula (even better would be to fully understand the formula, I
admit
> ;-)
>
> Anybody back from summer vacations yet?
> / Regards !
>
>

Thank you Tom, for your rapid answer. As you have helped me before, now you
are here again...

You suggest me to use a Listbox... I stranded on that because I want to have
a uniqe RBG color as backcolor and I can't find out if you can do that.
Therefore, I tried with the verification list in the Excel GUI instead.

Do you know a way of either fill the box with a uniqe RBG color or create a
new range of nonblanks only?

/Kindest Regards

"Tom Ogilvy" <twogilvy@msn.com> skrev i meddelandet
news:uoVtUPDnFHA.2472@tk2msftngp13.phx.gbl...
> Use code to fill the listbox.
>
>
> ListBox1.rowsource = ""
> Listbox1.Clear
> for each cell in Range("ListSource")
> if cell.Text <> "" then
> Listbox1.AddItem cell.value
> end if
> next
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Maria J-son" <NotAEmail@hotmail.com> wrote in message
> news:uUm7nMDnFHA.3936@TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a source range containing a mix of filled and empty cells with
>> name
>> "ListSource"
>>
>> A1: 111
>> A2: 222
>> A3:
>> A4: 444
>> A5: 555
>> A6:
>> A7: 777
>> A8:
>> A9:
>> A10:
>>
>> In the drop down verification on another sheet I refere to "=ListSource"
> and
>> there it is - all cells including the empty ones. I want the list to
>> show:
>>
>> 111
>> 222
>> 444
>> 555
>> 777
>>
>> The ListSource has to be in a row, therefore can I not create the list
> just
>> with a Autofilter macro...
>>
>> I have also tried the 'nonVBA way' with John Walkenbach's forumla
> "Returning
>> Nonblank Cells from a Range" without been able to reconstruct his result
>> (oh, yes - i used ctr-shift-enter to make arrays).
>>
> {=IF(ISERR(SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1
> :"&ROWS(Data))))),"",INDEX(Data,SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Dat
> a)))),ROW(INDIRECT("1:"&ROWS(Data))))))}
>>
>> For the very interested i translated it to swedish
>>
> {=OM(ÄRFEL(MINSTA(OM(YourData<>"";RAD(INDIREKT("1:"&RADER(YourData))));RAD(I
> NDIREKT("1:"&RADER(YourData)))));"";INDEX(YourData;MINSTA(OM(YourData<>"";RA
> D(INDIREKT("1:"&RADER(YourData))));RAD(INDIREKT("1:"&RADER(YourData))))))}
>> .
>>
>> Maybe I didn't get something right in the interpretation, but hey, it's
>> better to do it in VBA that I know, rather than trying to make it with
> this
>> long formula (even better would be to fully understand the formula, I
> admit
>> ;-)
>>
>> Anybody back from summer vacations yet?
>> / Regards !
>>
>>
>
>

Misunderstood that you want to use data validation.

John's formula works fine for me.

It is a multicell array formula, So Define you data as the named range
data. Then select as many cells as there are non empty cells and put in the
formula. Then do Ctrl+Shift+Enter and you should get you list.


Just to make sure you have a good formula, create the defined name Data.
Then select the muliple rows x 1 column range where you want the list
without blanks. then run this code:

Sub MakeFormula()
Dim sStr as String
sStr = "=IF(ISERR(SMALL(IF(Data<>"""",ROW(INDIRECT" & _
"(""1:""&ROWS(Data)))),ROW(INDIRECT(""1:""&ROWS" & _
"(Data))))),"""",INDEX(Data,SMALL(IF(Data<>"""",ROW" & _
"(INDIRECT(""1:""&ROWS(Data)))),ROW(INDIRECT" & _
"(""1:""&ROWS(Data))))))"
Selection.FormulaArray = sStr
End Sub

It should put in a working formula array entered.

--
Regards,
Tom Ogilvy


"Maria J-son" <NotAEmail@hotmail.com> wrote in message
news:OlDkdkDnFHA.3312@tk2msftngp13.phx.gbl...
> Thank you Tom, for your rapid answer. As you have helped me before, now
you
> are here again...
>
> You suggest me to use a Listbox... I stranded on that because I want to
have
> a uniqe RBG color as backcolor and I can't find out if you can do that.
> Therefore, I tried with the verification list in the Excel GUI instead.
>
> Do you know a way of either fill the box with a uniqe RBG color or create
a
> new range of nonblanks only?
>
> /Kindest Regards
>
> "Tom Ogilvy" <twogilvy@msn.com> skrev i meddelandet
> news:uoVtUPDnFHA.2472@tk2msftngp13.phx.gbl...
> > Use code to fill the listbox.
> >
> >
> > ListBox1.rowsource = ""
> > Listbox1.Clear
> > for each cell in Range("ListSource")
> > if cell.Text <> "" then
> > Listbox1.AddItem cell.value
> > end if
> > next
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Maria J-son" <NotAEmail@hotmail.com> wrote in message
> > news:uUm7nMDnFHA.3936@TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >> I have a source range containing a mix of filled and empty cells with
> >> name
> >> "ListSource"
> >>
> >> A1: 111
> >> A2: 222
> >> A3:
> >> A4: 444
> >> A5: 555
> >> A6:
> >> A7: 777
> >> A8:
> >> A9:
> >> A10:
> >>
> >> In the drop down verification on another sheet I refere to
"=ListSource"
> > and
> >> there it is - all cells including the empty ones. I want the list to
> >> show:
> >>
> >> 111
> >> 222
> >> 444
> >> 555
> >> 777
> >>
> >> The ListSource has to be in a row, therefore can I not create the list
> > just
> >> with a Autofilter macro...
> >>
> >> I have also tried the 'nonVBA way' with John Walkenbach's forumla
> > "Returning
> >> Nonblank Cells from a Range" without been able to reconstruct his
result
> >> (oh, yes - i used ctr-shift-enter to make arrays).
> >>
> >
{=IF(ISERR(SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1
> >
:"&ROWS(Data))))),"",INDEX(Data,SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Dat
> > a)))),ROW(INDIRECT("1:"&ROWS(Data))))))}
> >>
> >> For the very interested i translated it to swedish
> >>
> >
{=OM(ÄRFEL(MINSTA(OM(YourData<>"";RAD(INDIREKT("1:"&RADER(YourData))));RAD(I
> >
NDIREKT("1:"&RADER(YourData)))));"";INDEX(YourData;MINSTA(OM(YourData<>"";RA
> >
D(INDIREKT("1:"&RADER(YourData))));RAD(INDIREKT("1:"&RADER(YourData))))))}
> >> .
> >>
> >> Maybe I didn't get something right in the interpretation, but hey, it's
> >> better to do it in VBA that I know, rather than trying to make it with
> > this
> >> long formula (even better would be to fully understand the formula, I
> > admit
> >> ;-)
> >>
> >> Anybody back from summer vacations yet?
> >> / Regards !
> >>
> >>
> >
> >
>
>

Dear Tom,

You have once again maked it so simple - this time within some minutes you
enclosed a ready-to-use code to ensure the right syntax ! Incredible.

Yes, I hadn't find the right interpretation from english Excel functions to
swedish functions. It was the ISERR that I had choosen the wrong swedish
function.

Thank you once again.
/Regards


"Tom Ogilvy" <twogilvy@msn.com> skrev i meddelandet
news:OQxQYwDnFHA.3256@tk2msftngp13.phx.gbl...
> Misunderstood that you want to use data validation.
>
> John's formula works fine for me.
>
> It is a multicell array formula, So Define you data as the named range
> data. Then select as many cells as there are non empty cells and put in
> the
> formula. Then do Ctrl+Shift+Enter and you should get you list.
>
>
> Just to make sure you have a good formula, create the defined name Data.
> Then select the muliple rows x 1 column range where you want the list
> without blanks. then run this code:
>
> Sub MakeFormula()
> Dim sStr as String
> sStr = "=IF(ISERR(SMALL(IF(Data<>"""",ROW(INDIRECT" & _
> "(""1:""&ROWS(Data)))),ROW(INDIRECT(""1:""&ROWS" & _
> "(Data))))),"""",INDEX(Data,SMALL(IF(Data<>"""",ROW" & _
> "(INDIRECT(""1:""&ROWS(Data)))),ROW(INDIRECT" & _
> "(""1:""&ROWS(Data))))))"

=OM(ÄRF(MINSTA(OM(Data<>"";RAD(INDIREKT("1:"&RADER(Data))));RAD(INDIREKT("1:"&RADER(Data)))));"";INDEX(Data;MINSTA(OM(Data<>"";RAD(INDIREKT("1:"&RADER(Data))));RAD(INDIREKT("1:"&RADER(Data))))))
> Selection.FormulaArray = sStr
> End Sub
>
> It should put in a working formula array entered.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Maria J-son" <NotAEmail@hotmail.com> wrote in message
> news:OlDkdkDnFHA.3312@tk2msftngp13.phx.gbl...
>> Thank you Tom, for your rapid answer. As you have helped me before, now
> you
>> are here again...
>>
>> You suggest me to use a Listbox... I stranded on that because I want to
> have
>> a uniqe RBG color as backcolor and I can't find out if you can do that.
>> Therefore, I tried with the verification list in the Excel GUI instead.
>>
>> Do you know a way of either fill the box with a uniqe RBG color or create
> a
>> new range of nonblanks only?
>>
>> /Kindest Regards
>>
>> "Tom Ogilvy" <twogilvy@msn.com> skrev i meddelandet
>> news:uoVtUPDnFHA.2472@tk2msftngp13.phx.gbl...
>> > Use code to fill the listbox.
>> >
>> >
>> > ListBox1.rowsource = ""
>> > Listbox1.Clear
>> > for each cell in Range("ListSource")
>> > if cell.Text <> "" then
>> > Listbox1.AddItem cell.value
>> > end if
>> > next
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "Maria J-son" <NotAEmail@hotmail.com> wrote in message
>> > news:uUm7nMDnFHA.3936@TK2MSFTNGP10.phx.gbl...
>> >> Hi,
>> >> I have a source range containing a mix of filled and empty cells with
>> >> name
>> >> "ListSource"
>> >>
>> >> A1: 111
>> >> A2: 222
>> >> A3:
>> >> A4: 444
>> >> A5: 555
>> >> A6:
>> >> A7: 777
>> >> A8:
>> >> A9:
>> >> A10:
>> >>
>> >> In the drop down verification on another sheet I refere to
> "=ListSource"
>> > and
>> >> there it is - all cells including the empty ones. I want the list to
>> >> show:
>> >>
>> >> 111
>> >> 222
>> >> 444
>> >> 555
>> >> 777
>> >>
>> >> The ListSource has to be in a row, therefore can I not create the list
>> > just
>> >> with a Autofilter macro...
>> >>
>> >> I have also tried the 'nonVBA way' with John Walkenbach's forumla
>> > "Returning
>> >> Nonblank Cells from a Range" without been able to reconstruct his
> result
>> >> (oh, yes - i used ctr-shift-enter to make arrays).
>> >>
>> >
> {=IF(ISERR(SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1
>> >
> :"&ROWS(Data))))),"",INDEX(Data,SMALL(IF(Data<>"",ROW(INDIRECT("1:"&ROWS(Dat
>> > a)))),ROW(INDIRECT("1:"&ROWS(Data))))))}
>> >>
>> >> For the very interested i translated it to swedish
>> >>
>> >
> {=OM(ÄRFEL(MINSTA(OM(YourData<>"";RAD(INDIREKT("1:"&RADER(YourData))));RAD(I
>> >
> NDIREKT("1:"&RADER(YourData)))));"";INDEX(YourData;MINSTA(OM(YourData<>"";RA
>> >
> D(INDIREKT("1:"&RADER(YourData))));RAD(INDIREKT("1:"&RADER(YourData))))))}
>> >> .
=OM(ÄRF(MINSTA(OM(Data<>"";RAD(INDIREKT("1:"&RADER(Data))));RAD(INDIREKT("1:"&RADER(Data)))));"";INDEX(Data;MINSTA(OM(Data<>"";RAD(INDIREKT("1:"&RADER(Data))));RAD(INDIREKT("1:"&RADER(Data))))))
>> >>
>> >> Maybe I didn't get something right in the interpretation, but hey,
>> >> it's
>> >> better to do it in VBA that I know, rather than trying to make it with
>> > this
>> >> long formula (even better would be to fully understand the formula, I
>> > admit
>> >> ;-)
>> >>
>> >> Anybody back from summer vacations yet?
>> >> / Regards !
>> >>
>> >>
>> >
>> >
>>
>>
>
>