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,

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,COLUMN S(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" wrote in message
...
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 :)

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" wrote in message
...
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 :)

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,COLUMN S(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" wrote in message
...
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 :)

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Kylie Rose" wrote in message
...
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,COLUMN S(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" wrote in message
...
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 :)

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","w ork"},{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 :)