names and addresses

**bobbydog** · 03-15-2009, 05:48 AM

forgive me, i am very new and only just joined
question

i want to be able to write all addresses , personal names, hobbies, likes/dislikes
where i can access certain types of info with a single click

ie addresses of all those who like theatre trips
or all those who like travel

can any of you good people here, show in an idiots guide how to do this

**Simon Lloyd** · 03-15-2009, 07:15 AM

Simply record them on one sheet (sheet 2) a single row for each person, then go to the menubar and EDIT>NAMES>DEFINE in the box at the top type Namez then in the refers to box at the bottom paste this =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

Now go to sheet 1 click A1 then on the menubar DATA>VALIDATION in the allow box choose LIST in the source box type =Namez click ok, now in B1 type =IF($A1="","",VLOOKUP($A1,Sheet2!$A:$N,2,FALSE)) (adjust the size of the range to suit) and copy across, now when you choose a name in A1 the rest fills in, you can copy both validation and formulae down too!

**bobbydog** · 03-15-2009, 07:31 AM

hi simon lloyd

thanx for that will do as requested

is it poss, to ask advice from time to time
again regards

**bobbydog** · 03-15-2009, 10:28 AM

opening up excwl 2007

where is the menu bar situated on the page
tried inputting the formula and it kept chucking me out
what an i doing wrong please

all i want is a list of manes and addresses with peoples likes/dislikes, and various other things
and to be able to reference them from a single word command
ie if people like theatre then all those who like theatre will appear only

i am on excel 2007 any takers on this idiots plight
regads

**Simon Lloyd** · 03-15-2009, 11:52 AM

Firstly the formilae i gave are samples that you can play around with, but it gives you an idea how to retrieve data when a keyword is used in a cell, to bring up lists of people will be a quite in depth but can be done with formula.

For an explanation on where menubars/toolbars were in xl2003 and where they are now in xl2007 look here http://www.thecodecage.com/forumz/ge...locations.html