Help with Formula (data validation maybe?)

[ratedr1]

I have a sheet that looks like this with phone #s filled in
a b c d e
home home2 car office office2
1 Tom
2 Bill
3 Steve
4 Harry
5 John

I have created a drop down menu for the names, and a dropdown menu for
the locations. What I want to do, is create a formula or something to
put in a box next to these menus so that when you select, for example,
Tom + home2 it will give you THAT #, and if you select Steve + car it
will give you THAT #, and so on

Any help would be GREATLY appreciated!
Scott

 

[Ardus Petus]

Your table:
A B C D E F
1 home home2 car office office2
2 Tom
3 Bill
4 Steve
5 Harry
6 John

Assuming name is in A8 and location in B8 enter formula in C8:
=INDEX(B2:F6,MATCH(A8,A2:A6,0),MATCH(B8,B1:F1,0))

HTH

 

[Frederick Chow]

Hi Scott,

1. Select A1:E5, and then Choose Insert | Name | Create, and then click OK.
2. Assume that:
a. you have made linked cells in H1, I1 to each of your dropdown menu
respectively,
b. In H2, enter "=INDEX(ListRange1, 1, H1)"
In I2, enter "=INDEX(ListRange2, ,I1, 1)"
where ListRange1 = {home, home2, ...} and ListRange2 = {Tom, Bill,
..... }
c. Enter the following formula in a cell

=ADDRESS(ROW(INDIRECT(H2) INDIRECT(H2)), COLUMN(INDIRECT(H2), INDIRECT(H2))

Hope this helps
Frederick Chow

 

[ratedr1]

thank you for the help. it does help, for a second sheet I am
doing...however I made a mistake when posting, and I gave some wrong
info. Here is the actual question that I meant to write. Here is my
sheet
A B C D E F
G
1 x xx xxx xxxx
xxxxx
2 Tom =b2 =B2*2.2 =b2*3.1 =b2*9 =b2*2

3 Bill =b3 =B3*2.4 =b3*3.2 =b3*8
=b3*4
4 Steve =b4 =B4*2.12 =b4*6.4 =b4*1 =b4*5
5 Harry =b5 =B5*1.94 =b5*5 =b5*1 =b5*2
6 John =b6 =B6*2.6 =b6*2 =b6*2
=b6*6

In the above example, I put a # in b and it multiplies it by the # in
the formula (obviously)
I have a drop down menu of the 5 names, and a drop down menu of the x,
xx, xxx, xxxx, xxxxx. I basically want to put a box in between these
cells where I can type in a # and it would display the result in the
4th box..like below (the NAME, #, X?, result are the column labels)

NAME # X?
Result
(drop down ) (enter manually) (drop down) ***
***would take the formula from the sheet above, and put the end result
here.
IF you can help I would greatly appreciate it, or if you know of a
better way to do this, it would be great as well. Thank you so much in
advance,
Scott

 

[Ardus Petus]

You will have to consider another approach.
The following example should fit your needs:
http://cjoint.com/?dwtb7HTh8d

HTH

 

[ratedr1]

THANK YOU! That was perfect...It is done!