How to create a drop down box as a heading

[Kylie Rose]

I am trying to use excel for a database and would like to create a drop down
box for the contact numbers of individuals, I would like everytime I select
an alternative heading (four of them) that the data then coincides to all
individuals in the database, ie, select fax numbers from a drop down box also
containing home, work and mobile numbers and then have all the individuals
fax numbers appear in that column. Is this possible??

Thanks to anyone taking the time to answer this.
Kylie

[Bob Phillips]

Kylie,

Assuming that the headings are in A1:D1, create a DV box, and with a type of
list, add this formula

=OFFSET(A1:D1,1,MATCH(G1,OFFSET(A1:D1,0,0,1,COLUMNS(A1:D1)),0)-1,COUNTA(OFFS
ET(A1:D1,1,MATCH(G1,OFFSET(A1:D1,0,0,1,COLUMNS(A1:D1)),0)-1,1000,1)),1)

This assumes the selected item is in G1

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Kylie Rose" <[email protected]> wrote in message
news:[email protected]...
> I am trying to use excel for a database and would like to create a drop
down
> box for the contact numbers of individuals, I would like everytime I
select
> an alternative heading (four of them) that the data then coincides to all
> individuals in the database, ie, select fax numbers from a drop down box
also
> containing home, work and mobile numbers and then have all the individuals
> fax numbers appear in that column. Is this possible??
>
> Thanks to anyone taking the time to answer this.
> Kylie

[Bob Phillips]

BTW, you will probably get a message saying there is an error, just continue
past it.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Kylie Rose" <[email protected]> wrote in message
news:[email protected]...
> I am trying to use excel for a database and would like to create a drop
down
> box for the contact numbers of individuals, I would like everytime I
select
> an alternative heading (four of them) that the data then coincides to all
> individuals in the database, ie, select fax numbers from a drop down box
also
> containing home, work and mobile numbers and then have all the individuals
> fax numbers appear in that column. Is this possible??
>
> Thanks to anyone taking the time to answer this.
> Kylie

[Kylie Rose]

Thank you very much for your time, it astounds me that people take the time
out to answer others problems here, it's great.

I'm ashamed to say I don't quite understand the answer you have provided but
will endeavour to work on it tomorrow when I have a clear head. I'm sure it
makes perfect sense.
Kind Regards
Kylie

"Bob Phillips" wrote:

> Kylie,
>
> Assuming that the headings are in A1:D1, create a DV box, and with a type of
> list, add this formula
>
> =OFFSET(A1:D1,1,MATCH(G1,OFFSET(A1:D1,0,0,1,COLUMNS(A1:D1)),0)-1,COUNTA(OFFS
> ET(A1:D1,1,MATCH(G1,OFFSET(A1:D1,0,0,1,COLUMNS(A1:D1)),0)-1,1000,1)),1)
>
> This assumes the selected item is in G1
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "Kylie Rose" <[email protected]> wrote in message
> news:[email protected]...
> > I am trying to use excel for a database and would like to create a drop
> down
> > box for the contact numbers of individuals, I would like everytime I
> select
> > an alternative heading (four of them) that the data then coincides to all
> > individuals in the database, ie, select fax numbers from a drop down box
> also
> > containing home, work and mobile numbers and then have all the individuals
> > fax numbers appear in that column. Is this possible??
> >
> > Thanks to anyone taking the time to answer this.
> > Kylie
>
>
>

[Bob Phillips]

I have posted an example at http://cjoint.com/?clpVZyNcIc

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Kylie Rose" <[email protected]> wrote in message
news:[email protected]...
> Thank you very much for your time, it astounds me that people take the
time
> out to answer others problems here, it's great.
>
> I'm ashamed to say I don't quite understand the answer you have provided
but
> will endeavour to work on it tomorrow when I have a clear head. I'm sure
it
> makes perfect sense.
> Kind Regards
> Kylie
>
> "Bob Phillips" wrote:
>
> > Kylie,
> >
> > Assuming that the headings are in A1:D1, create a DV box, and with a
type of
> > list, add this formula
> >
> >
=OFFSET(A1:D1,1,MATCH(G1,OFFSET(A1:D1,0,0,1,COLUMNS(A1:D1)),0)-1,COUNTA(OFFS
> > ET(A1:D1,1,MATCH(G1,OFFSET(A1:D1,0,0,1,COLUMNS(A1:D1)),0)-1,1000,1)),1)
> >
> > This assumes the selected item is in G1
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "Kylie Rose" <[email protected]> wrote in message
> > news:[email protected]...
> > > I am trying to use excel for a database and would like to create a
drop
> > down
> > > box for the contact numbers of individuals, I would like everytime I
> > select
> > > an alternative heading (four of them) that the data then coincides to
all
> > > individuals in the database, ie, select fax numbers from a drop down
box
> > also
> > > containing home, work and mobile numbers and then have all the
individuals
> > > fax numbers appear in that column. Is this possible??
> > >
> > > Thanks to anyone taking the time to answer this.
> > > Kylie
> >
> >
> >

[pinmaster]

How about something like this:

let's assume your database is set as follows:
sheet2
Columns-Header
A - Name
B - Address
C - City
D - State/Province
E - Zip/Postal Code
F - Home Phone
G - Work
H - Mobile
I - Fax

sheet1 you mange to pull these sets of data off of sheet2
Columns-Header
A - Name
B - Address
C - City
D - State/Province
E - Zip/Postal Coldes
F - your drop down list with "home,work,mobile,fax"

in F2 you could use something like

=IF(A2="","",VLOOKUP(A2,Sheet2!$A$2:$I$1000,LOOKUP($F$1,{"fax","home","mobile","work"},{9,6,8,7}),0))
copied down
{9,6,8,7} represents the column number in your database for
{"fax","home","mobile","work"}

or if you want to use this in a column within the database then use an
OFFSET formula

=OFFSET(A2,0,LOOKUP($F$1,{"fax","home","mobile","work"},{9,6,8,7})-1)
copied down

I forgot to put a column for e-mails but hopefully you get the idea


Hope this helps!
Jean-Guy





"Kylie Rose" wrote:

> I am trying to use excel for a database and would like to create a drop down
> box for the contact numbers of individuals, I would like everytime I select
> an alternative heading (four of them) that the data then coincides to all
> individuals in the database, ie, select fax numbers from a drop down box also
> containing home, work and mobile numbers and then have all the individuals
> fax numbers appear in that column. Is this possible??
>
> Thanks to anyone taking the time to answer this.
> Kylie