How do I link cells in the same spreadsheet

[dhalle]

I am sure this is dumb question but I am trying to link or associate data in a spreadsheet. This is a mutual fund application. What I am trying to do is to associate the data back to one cell and put that one cell in a drop down box so that when I click on the fund name in the drop down box it pulls the performance in the linked cells with it. For example:


Fund Name 1 yr 3 yr 5yr
ABC Mutual Fund 12 8.5 6.7

I want to be able to load all of the static data shown above into one worksheet then be be able to have drop down menus in another worksheet so I can click on ABC Mutual fund and it populates that cell with the fund name and then in the columns next to it it pulls in the 12, 8.5 and 6.7 automatically.

I am sure there probably is an easier way to accomplish what I am trying to do so any pointers would be appreciated.

thanks

Dan

[shg]

See Help for VLOOKUP.

[deadlyduck]

There are 2 parts to your request- to get the dropdown list of companies, you need to use data validation (and ideally named ranges).

To get the relevant data displayed, you need to use a VLOOKUP function.

I'm attaching a basic spreadsheet with the idea (see Sheet 1 and Sheet 2)- if you need more explanations feel free to continue this thread.

[dhalle]

I understand how to create a drop down list using the validation function. I tried to figure out the vlookup function to do what I am trying to do buy am not having much luck figuring it out.

Again what I want to be able to do is to pick a fund from the drop down list and have that option display the fund performance in the contiguous columns. I plan on using one worksheet to list all of the data and another worksheet where I can pick the fund I want from the drop down list and when it is selected it will display the associated data with that fund.

Can anyone help me with a formula to accomplish this? Should I post this in the programing area?

thanks

[shg]

No, you should post a workbook and explain what you want in context.

[Chris Bode]

Please follow following steps
1.Enter the data in sheet2
2.From the fund name column select all the fund names and name them as ‘Fund’ in the top left drop down box (Name box)
3.Move to sheet1, select column A.
4. From the menubar select data>validation
5.In the setting tab select list from the allow drop down list and enter following text in the source text box
=Fund
6.Click ok and you will see a drop down appearing in column A on selecting a cell
7.Now, select a cell in column B (say B1) and enter following lookup formula
=VLOOKUP(A1,Sheet2!A2:D3,2,FALSE)
Similarly add following formula in C1
=VLOOKUP(A1,Sheet2!A2:D3,3,FALSE)
add following formula in D1
=VLOOKUP(A1,Sheet2!A2:D3,4,FALSE)


Chris

[deadlyduck]

Again what I want to be able to do is to pick a fund from the drop down list and have that option display the fund performance in the contiguous columns.
Can anyone help me with a formula to accomplish this?

Did you check out Sheet 2 of my attachment- it has this feature set up!
Equally, the steps outlined by Chris Bode achieve the same result.

[dhalle]

Thank you Chris the instructions you gave me worked great...so my first question is solved. I now have another one. How do I use the VLOOKUP function to display text. I have a column of text next to the columns of numbers that i want to display but it doesnt seem to work with displaying text. Any help would be great. You have helped me a ton.

thanks

Dan