Matt H
Member
Registered: 11th Sep 01
Location: South Yorkshire
User status: Offline
|
Right
I want to create a drop down list which will carry across other data
So, to give a basic example
Cat = 5
Dog = 10
Badger = 15
This information will be in coloumn A + B respectively
The drop down list is to contain the animal & will be in coloumn C. I can manage this bit
I then want the 'price' of each animal to appear in coloumn D once you've selected from the drop down list
Stuck as a fuck  Help!
|
James_DT
Member
Registered: 9th Apr 04
Location: Cambridgeshire
User status: Offline
|
Use VLOOKUP in column D.
code:
=VLOOKUP($C1,$A$1:$B$3,2)
Where $A$1:$B$3 is the range of your list of animals and values.
edit: Actually, that only works if the list of animals is in alphabetical order.
[Edited on 16-11-2009 by James_DT]
|
Matt H
Member
Registered: 11th Sep 01
Location: South Yorkshire
User status: Offline
|
Are they dollar signs? 
|
James_DT
Member
Registered: 9th Apr 04
Location: Cambridgeshire
User status: Offline
|
Yes, but use this instead.
code:
=VLOOKUP($C1,$A$1:$B$3,2,0)
That won't need the list to be sorted.
The dollar signs just mean that Excel won't change those references if you move around the cell that the formula is in.
|
Matt H
Member
Registered: 11th Sep 01
Location: South Yorkshire
User status: Offline
|
Thanks 
Just so I understand
It looks up the value in C1 (the animal from A)
then picks the corresponding item from B (based on what number is next to it?)
What's the 2,0 for?
Sorry to be a dumbass
|
James_DT
Member
Registered: 9th Apr 04
Location: Cambridgeshire
User status: Offline
|
It looks up the value in C1 against the list of animals and values in the A & B columns (the range A1:B3 covers both columns), and then looks at the second column in that range (which is what the 2 does, if you had a third column you could change it to a 3 and so on).
The 0 at the end just tells VLOOKUP that the list isn't sorted.
|
Matt H
Member
Registered: 11th Sep 01
Location: South Yorkshire
User status: Offline
|
and, would this change dramatically if it were to be on a seperate workpage of a workbook?
|
James_DT
Member
Registered: 9th Apr 04
Location: Cambridgeshire
User status: Offline
|
No, just add the worksheet that contains the data to the reference.
If the list of animals and prices is on the sheet titled Worksheet 1:
code:
=VLOOKUP(C1, 'Worksheet 1'!A1:B3, 2, 0)
|
Matt H
Member
Registered: 11th Sep 01
Location: South Yorkshire
User status: Offline
|
Didn't work
|
