Drop down menu with content from another spreadsheet in Excel 2010 1

[Nostradamus]

Hi.

I have two spreadsheets. Let's call them X and Y

In X I have two columns which I would like to populate with information from two columns in spreadsheet Y.

spreadsheet Y containt the following two columns in $A1:$A3 and $B1:$B3
company x adress to company x
company y adress to company y
company Z adress to company Z

In spreadsheet X I'd like to have a drop down list in $A1 that picks up the company names (and adress) from spreadsheet Y.

I've managed to create a drop down menu in spreadsheat X (cell $A1) that picks up information from ONE column ($A$1:$A$3) i Y.
I did this using data validation from a list and choose =Y!$A$1:$A$3

However when I choose company x, I want $B1 in spreadsheet X to automatically pick up the company adress for the chosen company.
Can I do this using data validation in any way? Can I link or group the columns together so that a change in one column affects the other.

Hard to explain correctly. Hope you understand what I mean.

Thanks

 

[SkipVought]

hi,

You can have the COMPANY list as a Data Validation -- LIST drop down in one cell and in the adjacent cell, display the corresponding address, using the DV selection as the lookup value in a formula.

FIRST, your data in sheet Y ouhg to be structured as a TABLE, with HEADINGS in row 1 and the data in rows 2:4.

SECOND, use Named Ranges for you table using Formulas > Define Names > Create From Selection -- Names in TOP row

I used Company and Address as the Headings and consequently the Range Names.

So then in the DV -- List, all you need do is reference Company, and the company names will appear in the in-cell drop down.

Assuming that you DV is in cell A1, the adjacent cell formula would be
[tt]
=Index(Address,Match(a1,Company,0),1)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

you can also use the VLOOKUP command

=VLOOKUP(A1,'[SPREADSHEET Y.xlsx]Sheet1'!$A$1:$B$4,2,FALSE)

 

[SkipVought]

Did you try it?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Nostradamus]

I've been trying back and forth for a while now.

It's quite frustrating when I'm testing with a swedish version of MS Office 2010. The swedish development and translation team must have had busy days when this version was introduced. For instance VLOOKUP is called VTSÖK in sweden. In Office 2003 it was apparently called LETARAD. I guess it doesn't make sense to you, including weird characters like Ö. However it doesn't make ANY sense to me neither and this is my native language. I have no idea what VTSÖK is short for, and on top of that they have ; as separator instead of ,
They've also translated true/false to swedish SANN/FALSK (called SANT and FALSKT in Office 2003) to further annoy me.

I did look into the INDEX function but I never got it to work. Perhaps I was doing it wrong or maybe I was lost in translation, sort of speak.

I managed to do what I wanted using the swedish version of VLOOKUP.
This ended up beeing the magic formula:

=VTSÖK(A1;Y!A1:B4;2;FALSK)

Thanks a lot for your input and quick responses. Really appreciate it.