Automatically adding info from other cells

[Sentinel]

I can't seem to do the following in Excel 2000 (or even if it's possible).
Any advice?:

A Workbook contains two worksheets, 'MyList' & 'Stations'. The 'Stations'
Worksheet has three columns containing 170 rows of
information. Column 'A' contains lists names of Underground stations,
Column 'B' contains the station line information and Column 'C' contains
the colour of the line. An example of the first ten rows is shown below.

A B C
1 Knightsbridge Piccadilly Line Dark Blue
2 Marble Arch Central Line Red
3 Wimbledon Park District Line Green
4 High Barnet Northern Line Black
5 Lambeth North Bakerloo Line Brown
6 Kingsbury Jubilee Grey
7 Hanger Lane Central Line Red
8 Sudbury Town Piccadilly Line Dark Blue
9 Preston Road Metropolitan Purple
10 Edgware Northern Line Black

etc. to 170

The 'MyList' Worksheet is currently blank. The information input into the
cells in Column 'A' of the 'MyList' Worksheet will be the name of one of
the stations in Column A of the 'Stations Worksheet'. Is it possible to
make information from the 'Stations' Worksheet appear on the 'MyList'
Worksheet based on what I type in Column 'A' of 'MyList'? For example:

On the 'MyList' Worksheet, when I type in 'High Barnet' in cell A2 I want
'Northern Line' to appear in cell C2 and 'Black' to appear in E2

and

when I type in 'Marble Arch' in cell A32 I want 'Central Line' to appear
in cell C32 and 'Red' to appear in E32.

Any help appriciated! Thanks.

 

[RK]

Yes, you can do that, and it's not too tough. You might
have two issues there. The first one is obvious once you
know it, the second is the functional answer to your question.

1) you can reference cells in other worksheets just by
going there and clicking on them. I assume you know that,
but just in case - an example. If you want to refer to a
cell in the 'Stations' worksheet from your 'mylist'
worksheet. Just change worksheets and click the cell of
interest while editing a function, and hit return. It
sticks the reference in for you, without all the nasty
typing of quotes etc..

2) To do the lookup function is easy. Just use the VLOOKUP
or HLOOKUP functions. Read the help explanation for them,
particularly the examples and it will be clear. In a
nutshell though,
=VLOOKUP(Cell,'Stations'!range-of-cells,2, FALSE)

or something similar will do it, where you fill in the
appropriate cell references.

Ross.

 

[Ray]

In cell c2 enter =vlookup(a2,stations!a1:c170,2,2)
In cell e2 enter =vlookup(a2,stations!a1:c170,3,2)
Copy these formulas down the column and you should be good
to go

 

[Sentinel]

Worked perfectly! Thanks!

In cell c2 enter =vlookup(a2,stations!a1:c170,2,2)
In cell e2 enter =vlookup(a2,stations!a1:c170,3,2)
Copy these formulas down the column and you should be good
to go