How do I autofill combo boxes with their destination cell?

[Defoes Right Boot]

I have a combo box in cell A1 with its destination cell set as B1; I can
auto-copy the combo box using the normal drag & drop autofill to get new
combo boxes all the way down column A, but the destination cell for all the
new combo boxes is still set as B1.

Is there any way I can get it to automatically increase the row number of
the destination cell for each new combo box (I have about 2000 rows to do so
don't really want to have to go into each one to manually set the destination
cell!)

Any help very much appreciated!

[Arvi Laanemets]

Use relative reference instead of absolute, i.e.
=B1 or =$B1
instead of
=$B$1
as data validation source in A1.


--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"Defoes Right Boot" <[email protected]> wrote in
message news:[email protected]...
> I have a combo box in cell A1 with its destination cell set as B1; I can
> auto-copy the combo box using the normal drag & drop autofill to get new
> combo boxes all the way down column A, but the destination cell for all
the
> new combo boxes is still set as B1.
>
> Is there any way I can get it to automatically increase the row number of
> the destination cell for each new combo box (I have about 2000 rows to do
so
> don't really want to have to go into each one to manually set the
destination
> cell!)
>
> Any help very much appreciated!

[Defoes Right Boot]

Did that but still doesn't make a difference...

Have found another way round it now though (using Access instead!)

Thanks anyway.

"Arvi Laanemets" wrote:

> Use relative reference instead of absolute, i.e.
> =B1 or =$B1
> instead of
> =$B$1
> as data validation source in A1.
>
>
> --
> When sending mail, use address arvil<at>tarkon.ee
> Arvi Laanemets
>
>
> "Defoes Right Boot" <[email protected]> wrote in
> message news:[email protected]...
> > I have a combo box in cell A1 with its destination cell set as B1; I can
> > auto-copy the combo box using the normal drag & drop autofill to get new
> > combo boxes all the way down column A, but the destination cell for all
> the
> > new combo boxes is still set as B1.
> >
> > Is there any way I can get it to automatically increase the row number of
> > the destination cell for each new combo box (I have about 2000 rows to do
> so
> > don't really want to have to go into each one to manually set the
> destination
> > cell!)
> >
> > Any help very much appreciated!
>
>
>

[Arvi Laanemets]

Hi

Then celect cell B1 and define a named range p.e. ListSource with RefersTo
field
=SheetName!$B1
(Replace sheet name here accordingly to real one. And don't forget to make
the row reference relative!)

For A1, set list source to
=ListSource
and copy A1 down.


--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"Defoes Right Boot" <[email protected]> wrote in
message news:[email protected]...
> Did that but still doesn't make a difference...
>
> Have found another way round it now though (using Access instead!)
>
> Thanks anyway.
>
> "Arvi Laanemets" wrote:
>
> > Use relative reference instead of absolute, i.e.
> > =B1 or =$B1
> > instead of
> > =$B$1
> > as data validation source in A1.
> >
> >
> > --
> > When sending mail, use address arvil<at>tarkon.ee
> > Arvi Laanemets
> >
> >
> > "Defoes Right Boot" <[email protected]> wrote in
> > message news:[email protected]...
> > > I have a combo box in cell A1 with its destination cell set as B1; I
can
> > > auto-copy the combo box using the normal drag & drop autofill to get
new
> > > combo boxes all the way down column A, but the destination cell for
all
> > the
> > > new combo boxes is still set as B1.
> > >
> > > Is there any way I can get it to automatically increase the row number
of
> > > the destination cell for each new combo box (I have about 2000 rows to
do
> > so
> > > don't really want to have to go into each one to manually set the
> > destination
> > > cell!)
> > >
> > > Any help very much appreciated!
> >
> >
> >