VLOOKUP??

[Ket]

Hello,

I am using Excel 2003 and Windows XP Pro.

I have a data range which is 4 columns wide and x number of rows
(there is no maximum for x as this may change)

The columns are Name, Start date, Finish Date, Coursename.

How can I create a data validation list that operates in such a way,
that if a persons name is selected from the list, the corresponding
dates and course name appear underneath.

I am unable to use the Data - Filter option as the source data and
results are on different worksheets.

Thanks in advance.

Ket

 

[Frank Kabel]

Hi
if you place the drop down on a separate sheet in cell A1 (and the data
is on sheet 1) try the following formula in A2
=VLOOKUP($A$1,'sheet1'!$A$1:$E$1000,ROW(),0)
copy down

 

[rbanks]

Try this.

Attachment filename: sample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=470747

 

[Ket]

Excellent - Thanks very much

 

[Ket]

One question - What if a user has taken more than one course? Can it
be set up to show everything that a user has completed?

 

[rbanks]

OK,

Look at this.

Notes: I only put multiple courses in the data for Ken ambercrombie.

Important: For the Index and Match functions to work, you much sor
your source data by name first. You can do this manually, or with
simple macro

Attachment filename: sampleii.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=47111

 

[rbanks]

OK,

Look at this.

Notes: I only put multiple courses in the data for Ken ambercrombie.

Important: For the Index and Match functions to work, you much sor
your source data by name first. You can do this manually, or with
simple macro

Attachment filename: sampleii.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=47112

 

[Ket]

Works a treat - Thanks very much!