Look up matrix = display value ? Excel 2003 1

[wildcard100001]

Excel 2003

Column A has a list of locations(titled: calling from), Row 7 has the same locations listed (Calling to). The table is filled out with digit dial strings users would use to call a given location.

I want to add a "lookup calculator" at the top of the spreadsheet that has a drop down menu of column A and a drop down menu of row 7. I want the lookup to show the referenced text/value.
Example:
Dallas Boston Seattle
Dallas xxxx 21xxxx 22xxxx
Boston 20xxxx xxxx 22xxxx
Seattle 20xxxx 21xxxx xxxx

If I'm in Boston and want to call Seattle. In the lookup drop down I'd pick Boston in the call from field, then pick Seattle in the call to field. The result would show 22xxxx.

How do I do this? Seems simple enough... however I've spent way to much time trying to figure it out.

Thanks in advance,

Wildcard

 

[SkipVought]

Hi,

please post a SAMPLE of the list of locations table you refer to.

It is confusing where the one list is in Column A and the other list is in ROW 7.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wildcard100001]

See my example in my original post... if Row 7 is confusing assume row 1.

Dallas Boston Seattle
Dallas xxxx 21xxxx 22xxxx
Boston 20xxxx xxxx 22xxxx
Seattle 20xxxx 21xxxx xxxx

If I'm in Boston and want to call Seattle. In the lookup drop down I'd pick Boston in the call from field, then pick Seattle in the call to field. The result would show 22xxxx.

How do I program this? I want to automatically cross reference... by a drop down menu... instead of looking at columns and rows..

Thanks,

Wildcard

 

[SkipVought]

I NEED EXAMPLE SOURCE DATA.

Is that so difficult to understand?

SOURCE DATA PLEASE. Calling FROM, Calling TO -- as you would see it in a TABLE!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Whoa there.

Somewhere up above row 7, you need 2 cells that have Data validation
Specify a List for both of them that the reference points to all locations available (but only one set! You only want each location listed once). Now allow in-cell dropdown

Make sure both cells are set up in the exact same manner.

Let's assume those cells are B2 and D2

In another cell, your result cell, put this formula in:

[tt]
=INDEX($A$7:$X$50,MATCH($B$2,$A:$A,0),MATCH($D$2,$7:$7,0))
^or whatever
[/tt]

If nothing is selected, it will show #N/A, you can have the cells pre-filled with locations if you want to avoid this.

 

[wildcard100001]

Here is the source data:
A B C D
1 Dallas Boston Seattle
2 Dallas xxxx 21xxxx 22xxxx
3 Boston 20xxxx xxxx 22xxxx
4 Seattle 20xxxx 21xxxx xxxx


Call from: (drop down box listing Dallas, Boston, Seattle)
Call To: (drop down box listing Dallas, Boston, Seattle)
Result: Shows cross reference above.

So if I picked Dallas and Dallas the result would be xxxx (I want it to show xxxx)

So if I picked Boston and Seattle the result would be 22xxxx (I want it to show 22xxxx)

No actual calculations... just show me the text depending on what is enter on the drop down box.

Thanks,

Wildcard

 

[SkipVought]

Change the COLUMN names as shown
[tt]
Dallas Boston Seattle
Dallas_ DDxx 21xxxx SSxxxx
Boston_ 2Dxxxx xxxx 2Sxxxx
Seattle_ 20xxxx 21xxxx xxSS
[/tt]
SELECT the entire table

Insert > Name > Cerate -- CHECK TOP and LEFT
[tt]
=Boston_ Seattle
[/tt]
returns 2Sxxxx




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wildcard100001]

Thanks Skip, I went with Gruuu's suggestion as I don't have to change values.

Thanks Gruuu - that did the trick... I had to mess with it a bit but it works great. I have a spread sheet that has many locations... this saves me and whom I distribute it to the time of looking it up visually on the matrix.

Thanks,

Wildcard